Sorting strings & numbers in SQL query

andrishelp
andrishelp used Ask the Experts™
on
TableName: cartrows
FieldName: sortorder and datatype : varchar
Data: 1, 5.23, ALL, S1, S2, S3, S4, 5.23, 11, ALL, S1, S2, S4.12, S5

When I write this query :
SELECT * FROM cartrows
ORDER BY
case isnumeric(replace(sortorder,',','.')) when 1 then cast(replace(sortorder,',','.') as float)
else ASCII(replace(sortorder,',','.'))  end

Result I got  this : 1, 5.23, 5.23, 11, ALL, ALL, S1, S2, S3, S4.12, S5, S1, S2, S4

Expected result : 1, 5.23, 5.23, 11, ALL, ALL, S1, S1, S2, S2, S3, S4, S4.12, S5

Can anyone please help me with this sorting?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
do you always have these values 1, 5.23, ALL, S1, S2, S3, S4, 5.23, 11, ALL, S1, S2, S4.12, S5 on your table?

If yes, then create a temp value (as sequence) and do the sort on them.

Commented:
You can try some thing like below. The t_short column is a temp one that will help the sorting/ordering.

;with cartrows as
(
select '1' vs,  1 t_short Union All
select '5.23', 2  Union All
select '5.23', 3 Union All
select '11', 4 Union All
select 'ALL', 5 Union All
select 'ALL', 6  Union All
select 'S1', 7   Union All
select 'S2', 8  Union All
select 'S3', 9   Union All
select 'S4.12', 10   Union All
select 'S5', 11   Union All
select 'S1', 12   Union All
select 'S2', 13   Union All
select 'S4',14  
)
select * from cartrows
order by isnumeric(replace(sortorder,',','.')) desc, sortorder
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
include the order by clause like below.. The other solution by knightEknight works as well.

;with cartrows as
(
select '1' vs,  1 t_short Union All
select '5.23', 2  Union All
select '5.23', 3 Union All
select '11', 4 Union All
select 'ALL', 5 Union All
select 'ALL', 6  Union All
select 'S1', 7   Union All
select 'S2', 8  Union All
select 'S3', 9   Union All
select 'S4.12', 10   Union All
select 'S5', 11   Union All
select 'S1', 12   Union All
select 'S2', 13   Union All
select 'S4',14  
)
select * from cartrows
order by  isnumeric(t_short) desc
What I meant was, add this to your query:

SELECT * FROM cartrows
ORDER BY
case isnumeric(replace(sortorder,',','.')) when 1 then cast(replace(sortorder,',','.') as float)
else ASCII(replace(sortorder,',','.'))  end, isnumeric(replace(sortorder,',','.')) ,  sortorder
and perhaps the very last sortorder should actually be: replace(sortorder,',','.')

Author

Commented:
sventhan:
No, the table does not always have these values 1, 5.23, ALL, S1, S2, S3, S4, 5.23, 11, ALL, S1, S2, S4.12, S5

knightEknight:
Your solution works but not for sorting numbers.
the result I got : 1, 11, 5.23, 5.23, ALL, ALL, S1, S1, S2, S2, S3, S4, S4.12, S5

Expected result: 1, 5.23, 5.23, 11, ALL, ALL, S1, S1, S2, S2, S3, S4, S4.12, S5



see my last

Author

Commented:
knightEknight:
When I refreshed the page, it wasn't there. It worked. Thanks for your help.
I just realized that the second isnumeric is redundant in the WHERE clause, so simply do this:

SELECT * FROM cartrows
ORDER BY
case isnumeric(replace(sortorder,',','.'))
  when 1 then cast(replace(sortorder,',','.') as float)
  else ASCII(replace(sortorder,',','.'))  end, replace(sortorder,',','.')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial