mariadbcasesql-order-by

How to correct the syntax for ORDER BY that works but the CASE logic is backwards?


I have the following folders table:

+----+--------+------+
| id | folder | user |
+----+--------+------+
| 1  | Inbox  | 0    |
+----+--------+------+
| 2  | Drafts | 0    |
+----+--------+------+
| 3  | Sent   | 0    |
+----+--------+------+
| 4  | Spam   | 0    |
+----+--------+------+
| 5  | Second | 1    |
+----+--------+------+
| 6  | First  | 1    |
+----+--------+------+

I'm required to SELECT the system folders ordered by the id first when the user is 0 (system) and then the user folders ordered by the name second. The returned folder order should look like the following:

  1. Inbox
  2. Drafts
  3. Sent
  4. Spam
  5. First
  6. Second

The results of my query works perfectly however the case logic is backwards!

SELECT id, folder, user 
FROM folders 
ORDER BY 
(CASE WHEN user='1' THEN folder END) ASC,
(CASE WHEN user='0' THEN user END) ASC;

If I switch the CASE order to the correct chronological/logical order I get the wrong results:

SELECT id, folder, user 
FROM folders 
ORDER BY 
(CASE WHEN user='0' THEN user END) ASC,
(CASE WHEN user='1' THEN folder END) ASC;

So placing the CASE syntax in the correct chronological order results in the wrong ordered output:

  1. First
  2. Second
  3. Inbox
  4. Drafts
  5. Sent
  6. Spam

How do I write my query to return the correct data in the correct order and have the query itself read and execute in a logical fashion?


Solution

  • You can do:

    SELECT id, folder, user
    FROM folders
    ORDER BY user, if (user=1, folder, id)
    

    See a dbfiddle

    Or, if you have a more ID's or bigger ID's, you can use:

    SELECT id, folder, user 
    FROM folders 
    ORDER BY user, if (user=1, folder, right(concat('000000', id),7) );
    
    

    See a dbfiddle