## Archive for July, 2015

## ORDER BY CASE

Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a `CASE`

statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this `ORDER BY`

clause as an example:

CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |

It raises the following error in MySQL for students:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN' at line 6 |

It raises the following error in Oracle for some students:

CASE * ERROR AT line 7: ORA-01785: ORDER BY item must be the NUMBER OF a SELECT-list expression |

So, I built a little test case to replicate the problem and error message they encountered:

SQL> SELECT 'Debit' AS filter FROM dual 2 UNION ALL 3 SELECT 'Credit' AS filter FROM dual 4 UNION ALL 5 SELECT 'Total' AS filter FROM dual 6 ORDER BY 7 CASE 8 WHEN filter = 'Debit' THEN 1 9 WHEN filter = 'Credit' THEN 2 10 WHEN filter = 'Total' THEN 3 11 END; |

They said, great but how can you fix it? That’s simple, with a Common Table Expression (CTE) in Oracle or with an inline view in MySQL. The Oracle CTE solution is:

1 WITH results AS 2 (SELECT 'Debit' AS filter FROM dual 3 UNION ALL 4 SELECT 'Credit' AS filter FROM dual 5 UNION ALL 6 SELECT 'Total' AS filter FROM dual) 7 SELECT filter 8 FROM results 9 ORDER BY 10 CASE 11 WHEN filter = 'Debit' THEN 1 12 WHEN filter = 'Credit' THEN 2 13 WHEN filter = 'Total' THEN 3 14 END; |

There are two MySQL solutions. One simply removes the `FROM dual`

clauses from the query components and the other uses an inline view in the `FROM`

clause. This is the inline view:

SELECT filter FROM (SELECT 'Debit' AS filter FROM dual UNION ALL SELECT 'Credit' AS filter FROM dual UNION ALL SELECT 'Total' AS filter FROM dual) resultset ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |

This is the solution without the `FROM dual`

clauses:

SELECT 'Debit' AS filter UNION ALL SELECT 'Credit' AS filter UNION ALL SELECT 'Total' AS filter ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |

Both MySQL solutions yield the following:

+--------+ | filter | +--------+ | Debit | | Credit | | Total | +--------+ 3 rows in set (0.00 sec) |

It puts the fabricating query inside a result set, and then lets you use the column alias to filter the set. If you have a better approach, please share it.