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.

Advertisements