Sorting results by a char(1) column

Question

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

Answer 1

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

Answer 2

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