ORDER BY CASE

Here is my ORDER BY Clause, and based on the @.Option parameter, I do
different order by. This works fine since the order is set from just one
column.
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END
The problem is that as I add more columns to do order by, it errors (like
the following).
ORDER BY CASE 1
WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
Priority DESC, IsApproved DESC
WHEN '2' THEN CompletedDate ASC, ProjectID DESC
WHEN '3' THEN Priority ASC, Priority DESC
END
It seems you cannot have more than one columns if you use case statement in
the order by clause. then what is the purpose of case if you can just do
sort by just one column?
Additionaly, can this be done other than dynamic query (and other than bunch
of if else statement)?
Thanks all
JOrdering by CASE using multiple columns is not, unfortunately, very
straightforward. You have to do something like:
ORDER BY
CASE @.Option
WHEN 1 THEN IsApproved
WHEN 2 THEN CompletedDate
WHEN 3 THEN Priority
END ASC,
CASE @.Option
WHEN 1 THEN CompletedDate
WHEN 2 THEN ProjectId,
WHEN 3 THEN Priority
END DESC,
..
This may or may not be better than using dynamic SQL -- it really depends on
just how dynamic/complex the conditions are.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
> in the order by clause. then what is the purpose of case if you can just
> do sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
> bunch of if else statement)?
> Thanks all
> J
>|||I believe that Case returns a value, not a clause, so your case function is
treated like a single column and cannot be an order by clause in itself. In
this case, you could try using several case statements, although there may
be a better way.
For Case = 3 you have priority listed twice, in two different orders. I
assume this is a typo. Basically, you need a case for each column, not each
sort order.
Try this approach. It is really tedious, but should work.
ORDER BY
CASE
WHEN @.Option = '1' THEN IsApproved ASC
WHEN @.Option = '2' THEN CompletedDate ASC
WHEN @.Option = '3' THEN Priority ASC
END
,CASE
WHEN @.Option = '1' THEN CompletedDate DESC
WHEN @.Option = '2' THEN ProjectID DESC
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN ProjectID DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN Priority DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN IsApproved DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
in
> the order by clause. then what is the purpose of case if you can just do
> sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
bunch
> of if else statement)?
> Thanks all
> J
>|||I missed on thing here...
As Adam has in his post, the ASC, DESC has to come after the END of the case
function.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eHtPc9GaGHA.440@.TK2MSFTNGP05.phx.gbl...
> I believe that Case returns a value, not a clause, so your case function
is
> treated like a single column and cannot be an order by clause in itself.
In
> this case, you could try using several case statements, although there may
> be a better way.
> For Case = 3 you have priority listed twice, in two different orders. I
> assume this is a typo. Basically, you need a case for each column, not
each
> sort order.
> Try this approach. It is really tedious, but should work.
> ORDER BY
> CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN CompletedDate
> WHEN @.Option = '3' THEN Priority
> END ASC
> ,CASE
> WHEN @.Option = '1' THEN CompletedDate
> WHEN @.Option = '2' THEN ProjectID
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN ProjectID
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN Priority
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> "Justin" <jyang@.ioutsource.info> wrote in message
> news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
one
(like
> in
do
> bunch
>|||you can try it this way. I can understand its painful but I don't see any
other option
ORDER BY CASE @.option
WHEN '1' THEN IsApproved
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END ASC,
CASE @.option
WHEN '1' THEN CompletedDate
WHEN '2' THEN ProjectID
WHEN '3' THEN Priority
END DESC
by the way
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC <-- ASC here will not work to my knowledge
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END|||Justin,
I'd suggest this approach:
ORDER BY
CASE WHEN @.Option = 1 THEN IsApproved END ASC,
CASE WHEN @.Option = 1 THEN CompletedDate END DESC,
CASE WHEN @.Option = 1 THEN ProjectID END DESC,
CASE WHEN @.Option = 2 THEN CompletedDate END ASC,
CASE WHEN @.Option = 2 THEN ProjectID END DESC,
CASE WHEN @.Option = 3 THEN Priority END ASC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC
It's relatively manageable, and it also avoids the type conversion
errors you can get when you have more than one CASE alternative.
Steve Kass
Drew University
Justin wrote:

>Here is my ORDER BY Clause, and based on the @.Option parameter, I do
>different order by. This works fine since the order is set from just one
>column.
>ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
>The problem is that as I add more columns to do order by, it errors (like
>the following).
>ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
>Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
>It seems you cannot have more than one columns if you use case statement in
>the order by clause. then what is the purpose of case if you can just do
>sort by just one column?
>Additionaly, can this be done other than dynamic query (and other than bunc
h
>of if else statement)?
>Thanks all
>J
>
>