14,732,816 members
See more:
I have an sql table, in which I need to select the data, that is 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 I have tried:

I have tried
select * from table
order by case when @SortOrder = 1 then c1,c2,c3
when @SortOrder = 2 then c2,c1,c3
when @SortOrder = 3 then c3,c1,c2
end
Posted
Updated 31-Mar-16 1:34am
jaket-cp 31-Mar-16 7:40am

have a read of:
https://msdn.microsoft.com/en-us/library/ms188385.aspx
ORDER BY Clause (Transact-SQL)
Specifying a conditional order

It should help you out, you should be able swap out the column with the variable @SortOrder.
You maybe required to have the case statement multiple times for each additional column sort.

## Solution 1

Try this

```declare @SortOrder int; declare @sql varchar(500);
declare @orderby varchar(50)
set @SortOrder  =3
set @orderby = case @SortOrder when 1 then 'c1,c2,c3' when 2 then 'c2,c1,c3' when 3 then 'c3,c2,c1' end
set @sql = 'select * from table1 order by '  + @orderby
exec (@sql)
```
Bhavika Mdm 31-Mar-16 6:44am

Tried... But didn't work for me.
Karthik_Mahalingam 31-Mar-16 6:51am

what error u r getting
make sure the column names are correct
Bhavika Mdm 31-Mar-16 8:18am

I have got a solution now.
I have written my code as below:
select * from table
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

It is working for me in this way.
Thank you for the help.
Karthik_Mahalingam 31-Mar-16 8:23am

Oh great. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard Deeming 115 Maciej Los 75 OriginalGriff 75 CHill60 60 Sandeep Mewara 60
 OriginalGriff 2,805 Richard MacCutchan 1,280 CPallini 1,048 thatraja 665 Maciej Los 637

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900