Order by numeric value in varchar -SQL

Question

1.00/5 (1 vote)

See more:

I have a column of int data type and other column is in varchar and I'm joining with union..

Current output
1
10
11
2
20
21
All

Excepted output
All
1
2
10
11
20
21

What I have tried:

Select cast(value as varchar)column1 from table1
Union
Select 'all' column1
Order by column1

Posted 4-Sep-17 16:57pm

Developer29

Updated 6-Sep-17 18:57pm

v3

Add a Solution

Comments

itsmypassion 5-Sep-17 1:32am

You can use CTE

3 solutions

Solution 1

ORDER BY[^] is what you need. Click on the link to see how to use it. All other SQL keywords can be found there also with full examples.

Posted 4-Sep-17 17:07pm

Graeme_Grant

Comments

Developer29 4-Sep-17 23:55pm

It is not order by properly....i tried...is there any other solution

Graeme_Grant 5-Sep-17 0:03am

It is sorting exactly as expected based on how you are declaring the value: cast(value as varchar)column1. Sorting is based on the "value type".

If the column was declared as a numerical value (int, float, date, etc), then it would sort differently to if the "value-type" was a text-based string (varchar). This is the same for ANY programming language, not just SQL.

If you DO want to numerically sort a string-based (varchar) numerical value, then you need to sort by length, then by value. This is not recommended as it will be much slower than the native "value type". CASTing also has a cost that will make your query slow.

Lastly, if you are ORDERing a column, then to make sure that it fast, you should have an INDEX configured for it.

Solution 3

If you are passing these results into a presentation layer then get rid of the union, use a simple order by and add the 'All' to the top of the list in the UI

Posted 5-Sep-17 0:44am

CHill60

Comments

Developer29 6-Sep-17 1:02am

I am using in report part

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)

itsmypassion · Accepted Answer · 2017-09-04T19:47:00

Solution 2

Please try this

SQL

select * from ( select cast( column1 as nvarchar(5)) as column1
from table1
union 
select column1 from table2) as x
order by 
    case 
        when isnumeric(column1) = 1 then cast(column1 as int)
        when isnumeric(left(column1,1)) = 0 then ascii(left(lower(column1),1)) 
        else 2147483647
    end

Posted 4-Sep-17 19:47pm

itsmypassion

Comments

Graeme_Grant 5-Sep-17 1:55am

You ignore the fact that there is a flaw in his table structure. Correct the floor and none of this is required if, in fact, your solution works.

Also, your solution is very inefficient and resource intensive and does not correct the actual problem, only a poor band-aid to a bigger problem.

itsmypassion 5-Sep-17 2:11am

YEs there is flaw in the table structure and also the solution is inefficient.But it gives expected results.

Graeme_Grant 5-Sep-17 2:49am

So your answer is to use a bigger hammer rather than fix the underlying problem first. Better to fix the cause than patch the problem. The benefit to fixing the cause performance wise is more important hence why I highly recommend avoiding your band-aid solution.

Developer29 6-Sep-17 1:02am

Can you describe the else part what it refers...

CHill60 6-Sep-17 14:59pm

You marked it as the solution! Silly if you still have questions