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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.