Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
select distinct(size) from tbl_size wHERE LIST_ID=17
DISPLAY MESSAGE
X
L
S
XL
HOW TO ARRANGE
ORDER BY SIZE ASC USING NOT WORKING RESULT
Posted

Add new field size_order in the query with numeric value
S = 1
L = 2
X = 3 /*Not sure what this is? If it is XS then set it at 0 :) */
XL = 4


Then order by size_order
 
Share this answer
 
Hi,

You can do something like :

SQL
select distinct(ts.size) from tbl_size ts
inner join (
Select Size,[Order] From (
Select 'S' Size, 0 [Order]
Union
Select 'L' Size, 1 [Order]
Union
Select 'X' Size, 2 [Order] 
Union
Select 'XL' Size, 3 [Order]
-- So on if required
)t ) Temp on Temp.Size=ts.Size
wHERE ts.LIST_ID=17 Order by Temp.Order
 
Share this answer
 
v2
Comments
vermanishad 29-Sep-14 6:28am    
Order is not my field error in sql server
Suvabrata Roy 29-Sep-14 6:44am    
I have created it at run time...
vermanishad 29-Sep-14 6:48am    
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Order'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Order'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Union'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Union'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.

sql error
Suvabrata Roy 29-Sep-14 7:01am    
select distinct(ts.size) from tbl_size ts
inner join (
Select Size,[Order] From (
Select 'S' Size, 0 [Order]
Union
Select 'L' Size, 1 [Order]
Union
Select 'X' Size, 2 [Order]
Union
Select 'XL' Size, 3 [Order]
-- So on if required
)t ) Temp on Temp.Size=ts.Size
wHERE ts.LIST_ID=17 Order by Temp.Order
vermanishad 29-Sep-14 7:20am    
Which sql server used you because my sql server [order] is not support

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900