How to sort multiple SQL columns based on a value

Question

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 31-Mar-16 0:14am

Bhavika Mdm

Updated 31-Mar-16 0:34am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Answer 1 · 2016-03-31T00:34:00

Solution 1

Try this

SQL

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)

Posted 31-Mar-16 0:34am

Karthik_Mahalingam

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. :)