CODEWITHSUNDEEP
sql-serversortingsql-order-byrow-numberover-clause
Bhavika
Bhavika

Reputation: 51

Order by multiple columns in SQL Server

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

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

Upvotes: 3

Views: 3308

Answers (5)

Pரதீப்
Pரதீப்

Reputation: 93694

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)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 4

JoeFletch
JoeFletch

Reputation: 3960

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

Upvotes: 0

sagi
sagi

Reputation: 40471

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

Upvotes: 4

Panayotis
Panayotis

Reputation: 1783

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)

Upvotes: 0

Related Questions