开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜