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.

  • What do you mean "by ourselves"? You control the sort order using ORDER BY. Could you give us an example please? – dwjv Nov 2 '15 at 14:22
  • For example, if say a column has the following rows: Apple Orange Banana ... I want third to be first, second to be third, you know, just order it the way I want it to appear. Was wondering if that was possible. – Ravi Nov 2 '15 at 14:24
  • Can you share e a sample unordered dataset and the expected output ? – Julien Vavasseur Nov 2 '15 at 14:24
  • Edited the question as well :) – Ravi Nov 2 '15 at 14:29
  • 1
    If you aren't using alpha-numeric sorting, then you likely have to create your own ordering mechanism though a new column. – Michael Gardner Nov 2 '15 at 14:31

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)

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.

|improve this answer|||||
  • Thank you for the response, I controlled this inside of the report in SSRS since it was just a visual requirement for two or three records. – Ravi Nov 3 '15 at 20:18
  • 1
    Nice, I am glad I could help you out. – Peter Elzinga Nov 4 '15 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.