CODEWITHSUNDEEP
mysqlsqldatabasecase
Backo
Backo

Reputation: 18881

What does it happen when using multiple CASE ... WHEN statements in the same SQL query?

After my previous question, I have an SQL query like the following:

SELECT * 
WHERE ... 
ORDER BY 
  CASE user_id WHEN 34 THEN 1 ELSE 2 END, 
  CASE status WHEN 'active' THEN 1 ELSE 2 END

As you can see in the above code, there are two CASE ... WHEN statements. What happens in this case? That is, how records will be ordered? May those "doubled" statements conflict?

UPDATE: I would like to make it to return records in this order: (1) records where user_id = 34 and status = 'active'; (2) records where status = 'active' (without user_id conditions); (3) all other records. How can I make that?

Upvotes: 0

Views: 168

Answers (2)

a1ex07
a1ex07

Reputation: 37384

First will come all records where user_id =34 with "active" status, then records with user_id=34 and status not "active", then records where user_id not equal 34 and status equals "active" , finally all remaining records where status is not "active"

Upvotes: 3

the_lotus
the_lotus

Reputation: 12748

Order by are done in the order that you write them.

Your record will be ordered:

[first]
user_id = 34, status = 'active'
user_id = 34, status <> 'active'
user_id <> 34, status = 'active'
user_id <> 34, status <> 'active'
[last]

Upvotes: 1

Related Questions