Tricks with ORDER BY

The ORDER BY clause in SQL Server is a bit more flexible than many people give it credit for.

In a training course I’m giving at the moment, we’ve just gone over a few of the lesser known uses, along with the widely used ones:

• The obvious: ordering by a column name

  ORDER BY advance
   Less obvious: ordering by an expression on a column
  ORDER BY id % 2 -- even IDs first, then odd
   Ordering by multiple fields
  ORDER BY id % 2 desc, price
         -- odd orders first by even/odd IDs, then by prices within each
  Ordering by visible column position
  SELECT id, customerid, price * quantity as "gross revenue"
  FROM orders
  ORDER BY 2
   Ordering by column aliases
  SELECT id, customerid, price * quantity as "gross revenue"
  FROM orders
  ORDER BY "gross revenue"
  

The last one is particularly interesting, because column aliases can’t be used in other places e.g. in the where clause or from clause.