Specific Sort order in SQL Query

Hello Experts,

Is there a way to create a custom sort order using tsql? I am querying a table in SQL Server 2005 and need to sort by a column but sorting ascending or descending won't work.

I have a table that looks like this:

Counter      Size      Amount
1                2S      1
2                 M      5
3                 3L     8
4                  S      9
5                3S    12
6                2L      6
7                  L      4
8               4S  0.3

I would like to create a custom sort by the "Size" column so the resulting query results look like this:

Size      Amount
3L         8
2L         6
L           4
M         5
S           9
2S         1
3S         12
4S         0.3

Does anyone know how to do this?

Who is Participating?
Alternatively you can have a table where all sizes will be listed. The table will also have a numeric column called SortOrder that will have the desired order. This way you will modify your query to LEFT JOIN to that table. If any new sizes come on board all you need to do is just add them to the table leaving the existing queries intact:
SELECT mytable.counter, mytable.size, mytable.amount
FROM   mytable LEFT JOIN sizes ON mytable.size = sizes.size
ORDER BY COALESCE(sizes.sortorder, 999) ASC

Open in new window

Ramachandar NCommented:
You can do this. But you need to know all possible sizes and include it in the query.

SELECT counter, size, amount
FROM   mytable
              WHEN size = '2L' THEN '2'
              WHEN size = 'L' THEN '3'
              WHEN size = 'M' THEN '4'
              WHEN size = 'S' THEN '5'
              WHEN size = '2S' THEN '6'
              WHEN size = '3S' THEN '7'
              WHEN size = '4S' THEN '8'
              ELSE size

Open in new window

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Along the lines of Ramachandar N's correct answer, I have an article out there called SQL Server CASE Solutions, and towards the bottom it illustrates CASE in ORDER BY clauses.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Saxitalis, do you still need help with this question?
SaxitalisAuthor Commented:
Sorry to take so long in Response - I was travelling...  This works great - Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.