order by case

I'm hoping there's an easy solution to this. When ordering with a case
statement this works:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname end
But if I want the case statement to sort on multiple columns:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname, firstname, customer_no end -- problem
It doesn't like the lastname, firstname, customer_no part.
Thanks for any suggestions.SG,
Try (as example(:
DECLARE @.SORT TINYINT
SET @.SORT = 0
SELECT *
FROM AUTHORS
ORDER BY CASE WHEN @.SORT = 1 THEN AU_LNAME ELSE STATE END, CITY
HTH
Jerry
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>|||CASE is an expression and can only return one value. Using data type
precedence, all possible outcomes must either be or be convertible to a
common data type. You cannot use CASE for logic flow, e.g. to replace IF /
ELSE.
So, you need to break this out a little differently.
ORDER BY CASE @.sort
WHEN 1 THEN customer_no
ELSE lastname END,
CASE @.sort WHEN 1 THEN NULL ELSE firstname END
Though unless customer_no is not a key, this will probably work equally
well:
ORDER BY CASE @.sort
WHEN 1 THEN customer_no END,
lastname, firstname
If customer_no is sorted on first, then the rest of the order by clause
won't have any effect, because every customer_no is unique. If the first
ORDER BY is NULL, then the lastname, firstname sort order is obeyed.
In either case, adding customer_no to the end of the order by is only going
to come into play in the rare case that users have identical first/last
names, in which case, does it really matter which customer_no is displayed
first?
For some more information on this topic, see http://www.aspfaq.com/2501
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>sql