fullstop
asked on
SQL sorting
This should be easy but I'm losing my mind here. I need to sort a table. As it is the table gives the results like this
Code Code description CodeType
6532 NNNNNNN 6
7853 MMMMMM 7
653 VVVVVV 6
785 LLLLLLLL 7
0253 XXXXXX 0
1200 Sme irrelevant 1
2250 BBBBBBBBBBB 2
225 XBXBX 2
120 XXXXXXXXX 1
025 ABCS 0
What I need is this
0253 same desc as above 0
025 same ----------------- 0
1200 same 1
1201 same ---------------- 1
1205 same---------------
120 same ------------- 1
2250 same----------------- 2
225 same---------- 2
6532 same-------------- 6
653 same ----------- 6
and so on. Basically what i need is Code column to be sorted by ASC order but each 3 length record should follow right after similarity ends. More precisely if code is 2250 and afterwards comes 2251, 2253,2254 this should end with 225. Code is nchar(10) field.
Hope I was clear
Thanks
Code Code description CodeType
6532 NNNNNNN 6
7853 MMMMMM 7
653 VVVVVV 6
785 LLLLLLLL 7
0253 XXXXXX 0
1200 Sme irrelevant 1
2250 BBBBBBBBBBB 2
225 XBXBX 2
120 XXXXXXXXX 1
025 ABCS 0
What I need is this
0253 same desc as above 0
025 same ----------------- 0
1200 same 1
1201 same ---------------- 1
1205 same---------------
120 same ------------- 1
2250 same----------------- 2
225 same---------- 2
6532 same-------------- 6
653 same ----------- 6
and so on. Basically what i need is Code column to be sorted by ASC order but each 3 length record should follow right after similarity ends. More precisely if code is 2250 and afterwards comes 2251, 2253,2254 this should end with 225. Code is nchar(10) field.
Hope I was clear
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
replace @t with your tablename
ASKER
Thanks, works great
select Code, [Code description], CodeType
from table1
order by case when (Len(Code) > 3) then Left(Code, 3) else Code end, [Code description], CodeType