Click here to Skip to main content
14,732,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
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.

1 solution

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)
   
Comments
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)




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