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:
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:
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?
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