Order by multiple columns in SQL Server

Question

I have a table in SQL Server in which I need to select the data, sorted based on a value. For example,

If the sort value is 1, then I need to sort by column1, column2 and then column3.
If the sort value is 2, then I need to sort by column2, column1 and then column3.
If the sort value is 3, then I need to sort by column3, column1 and then column2.

Can anyone tell me how can I achieve this in SQL without using if else as below:

IF @SortOrder = 1
THEN
    SELECT * 
    FROM table 
    ORDER BY c1, c2, c3
END

IF @SortOrder = 2
THEN
    SELECT * 
    FROM table 
    ORDER BY c2, c1, c3
END

IF @SortOrder = 3
THEN
    SELECT * 
    FROM table 
    ORDER BY c3, c1, c2
END

What are the data types of column1, column2 and column3? – David Rushton Mar 31 '16 at 11:00 — David Rushton, Mar 31 '16 at 11:00

score 4 · Accepted Answer · answered Mar 31 '16 at 10:08

4

You can use CASE EXPRESSION for conditional ordering:

SELECT * FROM Table
ORDER BY CASE WHEN @SortOrder = 1 then c1
              WHEN @SortOrder = 2 then c2
              ELSE c3
         END,
         CASE WHEN @SortOrder = 1 then c2
              ELSE  c1
         END,
         CASE WHEN @SortOrder in(1,2) then c3
              ELSE c2
         END

answered Mar 31 '16 at 10:08

sagi

40,026
6
59
84

I tried this. But as my first column (c1) is of date type. Hence, it is throwing an error as "Conversion failed when converting date and/or time from character string." – Bhavika Mar 31 '16 at 10:34
This doesn't suppose to even do conversation , so it doesn't make a lot of sense.. there is not problem in ordering by a date column. @Bhavika – sagi Mar 31 '16 at 10:39
@sagi i was considering writing the same. If the column types conflicts. This will not work – t-clausen.dk Mar 31 '16 at 10:50
It is showing this error when I put "else c1" part in the second case statement. – Bhavika Mar 31 '16 at 10:50

t-clausen.dk · Answer 2 · 2016-03-31T11:02:00.007

4

Using the CASE is correct. You need this syntax if the datatype conflicts in the columns(c1,c2,c3).

ORDER BY
  CASE WHEN @SortOrder = 1 THEN c1 END,
  CASE WHEN @SortOrder = 1 THEN c2 END,
  CASE WHEN @SortOrder = 1 THEN c3 END,
  CASE WHEN @SortOrder = 2 THEN c2 END,
  CASE WHEN @SortOrder = 2 THEN c1 END,
  CASE WHEN @SortOrder = 2 THEN c3 END,
  CASE WHEN @SortOrder = 3 THEN c3 END,
  CASE WHEN @SortOrder = 3 THEN c1 END,
  CASE WHEN @SortOrder = 3 THEN c2 END

edited Mar 31 '16 at 11:02

answered Mar 31 '16 at 10:53

t-clausen.dk

43,517
12
59
92

This worked very well for me. Thank you very much. :) – Bhavika Mar 31 '16 at 12:13

score 0 · Answer 3 · answered Mar 31 '16 at 10:03

0

Maybe try something like this: select * from table order by @SortOrder,c1,c2,c3 if the value represents the column order, else you van try adding an offset (eg @SortOrder + 4)

answered Mar 31 '16 at 10:03

Panayotis

1,743
1
17
30

score 0 · Answer 4 · answered Mar 31 '16 at 10:10

You can use a CASE statement in your ORDER BY clause and COALESCE the values. (Please note that you should may need to check performance on using this setup. I typically do not have problems, but figured that I would note it.)

SELECT *
FROM [TABLE]
ORDER BY
    CASE @SortOrder
    WHEN 1 THEN COALESCE(c1, N'') + N'-' + COALESCE(c2, N'') + N'-' + COALESCE(c3, N'')
    WHEN 2 THEN COALESCE(c2, N'') + N'-' + COALESCE(c1, N'') + N'-' + COALESCE(c3, N'')
    WHEN 3 THEN COALESCE(c3, N'') + N'-' + COALESCE(c1, N'') + N'-' + COALESCE(c2, N'')
        END

Or, if you want the value you sorted on to be returned in your results (but the Sort Order column must be the first column)

SELECT
    CASE @SortOrder
    WHEN 1 THEN COALESCE(c1, N'') + N'-' + COALESCE(c2, N'') + N'-' + COALESCE(c3, N'')
    WHEN 2 THEN COALESCE(c2, N'') + N'-' + COALESCE(c1, N'') + N'-' + COALESCE(c3, N'')
    WHEN 3 THEN COALESCE(c3, N'') + N'-' + COALESCE(c1, N'') + N'-' + COALESCE(c2, N'')
        END AS SortOrder
    *
FROM [TABLE]
ORDER BY 1

Pரதீப் · Answer 5 · 2016-03-31T17:33:45.137

0

I think dynamic query is best approach here for conditional ordering

DECLARE @sql       VARCHAR(max),
        @SortOrder INT = 2

SET @sql = 'select * from table order by ' 
            + CASE @SortOrder WHEN 1 THEN 'c1,c2,c3' WHEN 2 THEN 'c2,c1,c3' WHEN 3 THEN 'c3,c1,c2' END 

--print @sql
exec (@sql)

edited Mar 31 '16 at 17:33

answered Mar 31 '16 at 10:18

Pரதீப்

91,748
19
131
172

Order by multiple columns in SQL Server

5 Answers5