May 10, 2004 at 1:21 am
How do I make Custom Sorting ?
Hi, I have a Custom Sort Order by PSNo field.
The table "TEMP" contains one field PSNo. My "TEMP" table contains 25,000 data in this following format .
Sample data here
PSNO
--------
1
1A
1B
1AB
10A
10B
11A
11B
10
10AB
11AB
12
13
3
I want the Output as
PSNO
--------
1
1AB
1A
1B
3
10
10AB
10A
10B
11AB
11A
11B
12
13
Hope this helps
Partheeban
May 10, 2004 at 8:09 am
See if this meets your needs.
select * from TEMP
order by
CASE WHEN ISNUMERIC(LEFT(PSNO,2)) = 1 THEN CAST(LEFT(PSNO,2) AS TINYINT) ELSE CAST(LEFT(PSNO,1) AS TINYINT) END, -- This line will handle up to 2 values for numeric position
LEN(CASE WHEN ISNUMERIC(LEFT(PSNO,2)) = 1 THEN (CASE WHEN LEN(PSNO) > 2 THEN RIGHT(PSNO,LEN(PSNO)-2) END) ELSE (CASE WHEN LEN(PSNO) > 1 THEN RIGHT(PSNO,LEN(PSNO)-1) END) END) DESC -- This will get when characters exists and sort based on length desc, rest of order is natural.
May 11, 2004 at 3:16 pm
Hows this:
SELECT psno, CAST(REPLACE(REPLACE(REPLACE(psno, 'ab', '.1'), 'a', '.2'), 'b', '.3') AS float) AS sort_ordr
FROM temp
ORDER BY CAST(REPLACE(REPLACE(REPLACE(psno, 'ab', '.1'), 'a', '.2'), 'b', '.3') AS float)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply