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

admin管理员组

文章数量:1391987

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?

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?

• mariadb
• case
• sql-order-by

Share Improve this question Follow edited Mar 13 at 9:20 John asked Mar 13 at 5:40 JohnJohn 13.8k1515 gold badges111111 silver badges191191 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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

with cte (id, folder, user) as (
  select 1, 'Inbox', 0 union all
  select 2, 'Drafts', 0 union all
  select 3, 'Send', 0 union all
  select 4, 'Spam', 0 union all
  select 5, 'Second', 1 union all
  select 6, 'First', 2
)
SELECT *
FROM cte
ORDER BY user <=> 0 DESC,                -- put a group with user=0 first
         CASE WHEN user = 0 THEN id END, -- sort user=0 rows by id
         folder                          -- sort another rows by folder

id	folder	user
1	Inbox	0
2	Drafts	0
3	Send	0
4	Spam	0
6	First	2
5	Second	1

fiddle

本文标签： mariadbHow to correct the syntax for ORDER BY that works but the CASE logic is backwardsStack Overflow