Sorting results by a char(1) column
I have a stored procedure which basically does something like
select top 1 expiryDate, flag, (bunch of other columns)
from someTable
(bunch of joins)
order by expiryDate desc
So this will grab the record that expires last. This works for most cases, except some records have a flag that are just a char(1)
. Most of the time it's just Y
or N
.
So it'll return somet开发者_StackOverflowhing like
2010-12-31 N
2010-10-05 Y
2010-08-05 N
2010-03-01 F
2010-01-31 N
This works, most of the time, but is there any way to order it by the Flag column as well? So I'd want to group the results by Y
, then N
, and F
and any other flags can go last in any order. I thought this would just be an order by, but since the flags are not weighted by the alphabetic value, I'm a little stumped. (Note: These are not my tables, I don't know if using the characters like this was a good idea or not, but it's not something I can change).
You need the help of a CASE statement
Order By expiryDate desc,
CASE flag
When 'Y' THEN 1
When 'N' THEN 2
When 'F' THEN 3
ELSE 999
END ASC
You can order by several columns:
select top 1 expiryDate, flag, (bunch of other columns)
from someTable
(bunch of joins)
order by flag /* asc/desc */, expiryDate desc
精彩评论