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

Answer 1

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

Answer 2

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)

Answer 3

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

Answer 4

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)

Answer 5

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