sort string field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sort string field

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-27 : 15:35:22

Hi All
My query has fields CODE and Description both is varchar data type. The CODE values like: v89, 13.29, 456, 23.4 and so on. I would like to sort by CODE to get in result like: 13.29, 23.4, 456, v89. Is it possible?

Thanks.

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 15:37:16

ORDER BY CASE WHEN IsNumeric(CODE) = 1 THEN 1 ELSE 2 END, CODE

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-27 : 15:54:51

Thanks for replay.

The type CODE is varchar(10). I tried your code it works the same like simple ORDER BY.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-27 : 15:58:18

Add "200" and "3" to your data and see "200" come before "3" using an ordinary ORDER BY.
Stick with the suggestion given to you from Kristen.

N 56°04'39.26"
E 12°55'05.63"

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-27 : 16:10:45

I'm sorry. I forgot to say that view is source of the query. May be that is my problem of order.

Thanks.

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-27 : 16:46:51

I found decision.

Now how to use ORDER BY if SELECT has UNION.

Thanks.

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 17:02:43

SELECT *
FROM
(
SELECT Col1, COl2, ...
FROM TableA
UNION
SELECT Col1, COl2, ...
FROM TableB
)
ORDER BY Col1, Col2

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-27 : 18:04:12

Kristen thanks for replay.

I tried your code and got error message:
Incorrect syntax near the keyword 'order'. How to fix?

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-27 : 18:06:52

quote:
Originally posted by eugz

Kristen thanks for replay.

I tried your code and got error message:
Incorrect syntax near the keyword 'order'. How to fix?

Thanks.

You need to alias the derived table:

SELECT *
FROM
(
SELECT Col1, COl2, ...
FROM TableA
UNION
SELECT Col1, COl2, ...
FROM TableB
) AS T
ORDER BY Col1, Col2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 06:28:39

quote:
Originally posted by Kristen

ORDER BY CASE WHEN IsNumeric(CODE) = 1 THEN 1 ELSE 2 END, CODE

I hope you know isnumeric() is not fully reliable

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 06:47:38

I know that ... you know I know that

... but O/P might not ... although I did consider that and thought it was probably "good enough" for a Sort order.

ORDER BY CASE WHEN IsNumeric(CODE) = 1 AND CODE NOT LIKE '%[^-0-9.]%' THEN 1 ELSE 2 END, CODE

might be a little bit better. Still not 100% on Numeric test though.

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-28 : 10:33:16

Thanks everybody for help.

In my case I little modify ORDER BY and it start to work by this way

ORDER BY IsNumeric(CODE) desc, CASE When IsNumeric(CODE) = 1 Then cast(CODE as decimal) Else null End, CODE

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources