MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for July, 2015

ORDER BY CASE

with 8 comments

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.

Written by maclochlainn

July 8th, 2015 at 10:06 pm

Posted in MySQL,Oracle,Oracle 11g,Oracle 12c,sql

Tagged with ,