Custom order by in SQL server like P, A, L, H

sql sql-server sql-order-by
mhd noufel picture mhd noufel · Oct 5, 2013 · Viewed 46.1k times · Source

Not ASC or DESC.... Order by custom...

I have tried using case but not successfully

SELECT * FROM Customers
ORDER BY case country
when 'P' then 1 …

This is what I want:

Answer

juergen d picture juergen d · Oct 5, 2013 
SELECT * FROM Customers
ORDER BY case when country = 'P' then 1
              when country = 'A' then 2
              when country = 'L' then 3
              when country = 'H' then 4
              else 5
         end asc

Related questions

How to update and order by using ms sql

Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher …

Read More
Combining ORDER BY AND UNION in SQL Server

How can I get first record of a table and last record of a table in one result-set? This Query fails SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ORDER BY …

Read More
SQL - ROW_NUMBER () OVER (ORDER BY) not working

It doesn't matter what I put in the ORDER BY clause, the order of the result set doesn't change. WITH Results AS ( SELECT DISTINCT MessageThreadUsers.threadFK, MessageThreads.threadDate, Messages.MessageBody, Messages.senderFK, Users.userFullName AS senderFullName, ROW_NUMBER() OVER (ORDER …

Read More