SQL Server – How to Control Order of Columns in Resultset

sql server

Is it possible to control the sort order ourselves in a result set? So if I want to give it a custom order of appearance (instead of the ASC or DESC order), how can I go about doing it? I have read some answers on Stackoverflow which talk about using CASE statements in ORDER BY, but I do not understand how that works and how to make it work to get what I want.

enter image description here

In the above column for example, I want, say, Mitchell to be last, Carson to be first, Reiter to be fifth, etc.

Best Answer

You could create a new ordering table with sort order and link that to your original table. Then sort it by the sort order. Like this:

DECLARE @SortTable TABLE (LastName nvarchar(50), SortOrder int)

INSERT INTO @SortTable 
VALUES        ('Carson', 1)
            , ('Vargas', 2)
            , ('Ansman-Wolfe', 3)
            , ('Pak', 4)
            , ('Reiter', 5)
            , ('Varkey Chudukatil', 6)
            , ('Blythe', 7)
            , ('Mitchell', 8)

SELECT      YT.*
FROM        YourTable     AS YT
INNER JOIN  @SortTable    AS ST
    ON      ST.LastName   = YT.LastName
ORDER BY    ST.SortOrder

I used a table variable, but of course you can also use a normal table that you add in your database. Make sure you add all the possible values for lastname, otherwise you would miss out rows.

Related Question