Order by asc in SQL server

Question

1.00/5 (1 vote)

See more:

i need to sort the number from asscending value. sample data is

463919493       
463919493 02
463919493 02
463919493 03
463919493 01
463919493 03
463919493 01

i need to get output as

463919493       
463919493 01
463919493 01
463919493 02
463919493 02
463919493 03
463919493 03

and suppose if we have alphabet with number, how to sort this number in asscending

HO463919493       
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 01
HO463919493 03
HO463919493 01

Need to get output as:

HO463919493       
HO463919493 01
HO463919493 01
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 03

What I have tried:

463919493       
463919493 02
463919493 02
463919493 03
463919493 01
463919493 03
463919493 01

i need to get output as

463919493       
463919493 01
463919493 01
463919493 02
463919493 02
463919493 03
463919493 03

and suppose if we have alphabet with number, how to sort this number in asscending

HO463919493       
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 01
HO463919493 03
HO463919493 01

HO463919493       
HO463919493 01
HO463919493 01
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 03

Posted 26-Apr-18 2:37am

Vinodh Muthusamy

Updated 26-Apr-18 7:45am

RedDk

v2

Add a Solution

Comments

Maciej Los 26-Apr-18 8:38am

Do not post the same content! Please, provide information about your way to sort data. Describe what have failed.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-04-26T02:56:00

If you would like to treat text data as numeric, you have to convert it. For example:

SQL

SELECT * 
FROM (
    SELECT 'HO463919493' AS F1, NULL AS F2
    UNION ALL       
    SELECT 'HO463919493', '02'
	UNION ALL       
    SELECT 'HO463919493', '02'
	UNION ALL       
    SELECT 'HO463919493', '03'
	UNION ALL       
    SELECT 'HO463919493', '01'
	UNION ALL       
    SELECT 'HO463919493', '03'
	UNION ALL       
    SELECT 'HO463919493', '01'
) AS T
ORDER BY CONVERT(INT, SUBSTRING(F1, 3, LEN(F1))) ASC, CONVERT(INT, F2) ASC

Note: Above sample is for specific data, where first two letters are rejected during conversion.

Wendelius · Answer 2 · 2018-04-26T05:46:00

To add to both solutions: Why do you store the two separate values in a single column in the first place? Your SQL operations would be much easier if you would have the values both in separate columns. Then you could simply order the by using an ORDER BY clause like

SQL

ORDER BY Col2 ASC, Col1 ASC

Jochen Arndt · Answer 3 · 2018-04-26T02:53:00

Solution 1

You have not shown us any SQL query.

Usually you have to append

SQL

ORDER BY [columnname] ASC

to your query where columnname is the name of your second column. Because ASC is the default sort order it can be omitted.

You can also specify multiple columns or more complex expressions. See ORDER BY Clause (Transact-SQL) | Microsoft Docs[^] for more information.

Posted 26-Apr-18 2:53am

Jochen Arndt

Comments

Maciej Los 26-Apr-18 8:56am

OP wants to trat text data as a numeric. See my answer.

Jochen Arndt 26-Apr-18 9:05am

Ok. But with his example data a simple ORDER BY on the second column should do it. The first column may be added to the expression for sub-sorting. But in his examples that column has unique data.

Order by asc in SQL server

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0