Wednesday, November 08, 2006

Custom ordering for your results

... or, taming the ORDER BY clause.
Say you want to implement a custom ordering for your queries, as an example, you want to display each customer's orders with shipped orders first, then waiting orders and open orders last.
That's ordering by the 'status' column and you can't use alphabetical ordering!
In this case you'll have to implement some kind of logic in your order by clause, and a CASE is very handy, like this:

  1. SELECT * FROM SALES
  2. ORDER BY
  3. cust_no,
  4. CASE WHEN order_status = 'shipped' THEN 1
  5. WHEN order_status = 'waiting' THEN 2
  6. WHEN order_status = 'open' THEN 3
  7. ELSE 4
  8. END;

Note how the ordering is performed on the values substituted by the CASE statement and not
for the original column values.
This example is based on Firebird's EXAMPLE.FDB database.

The MySQL guys could also use a trick like the FIELD function

... ORDER BY FIELD(order_status,'shipped','waiting','open')

but this is a NON standard way of doing things that I don't recommend at all.

1 comment:

FTW said...

Thanks for this example. I was searching for a way to order by a SQL query by an arbitrary set of parameters. The CASE idea worked great.