andrishelp
asked on
Sorting strings & numbers in SQL query
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(sortorde
else ASCII(replace(sortorder,',
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?
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(sortorde
else ASCII(replace(sortorder,',
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?
Last Comment
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
;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(sortorde
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
;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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
ASKER
knightEknight:
When I refreshed the page, it wasn't there. It worked. Thanks for your help.
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(sortorde
when 1 then cast(replace(sortorder,','
else ASCII(replace(sortorder,',
SELECT * FROM cartrows
ORDER BY
case isnumeric(replace(sortorde
when 1 then cast(replace(sortorder,','
else ASCII(replace(sortorder,',
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
If yes, then create a temp value (as sequence) and do the sort on them.