... 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:
- SELECT * FROM SALES
- ORDER BY
- cust_no,
- CASE WHEN order_status = 'shipped' THEN 1
- WHEN order_status = 'waiting' THEN 2
- WHEN order_status = 'open' THEN 3
- ELSE 4
- 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.
…