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.