CODEWITHSUNDEEP
sqlsqlite
Ed Holloway-George
Ed Holloway-George

Reputation: 5149

Sort SQL based on two fields and extra constraint

I am attempting to write an SQLite Query that orders a table mytable firstly by a column called status_id and secondly by a column called name, but with an extra constraint using:

SELECT name, 
       status_id 
FROM mytable 
ORDER BY status_id, name

produces a table that is correctly sorted.

e.g (name, status): ("b", 1), ("a", 2), ("c", 2), ("b", 3), ("a", 4)...

However I require the same sort of output, but with any result having status_id equal to 2, being at the top of the results.

e.g. ("a", 2), ("c", 2), ("b", 1), ("b", 3), ("a", 4)...

I came up with this, but it doesn't function as required as the list isn't also subsorted by name:

SELECT name, 
       status_id 
FROM mytable 
ORDER BY CASE status_id 
WHEN 2 THEN 0 ELSE 1 END DESC

Upvotes: 1

Views: 56

Answers (3)

Declan_K
Declan_K

Reputation: 6826

You still need to sort by status_id and name, AFTER you sort by the CASE statement.

SELECT  name
        , status_id 
FROM    mytable 
ORDER BY 
        CASE WHEN status_id = 2 THEN 1 ELSE 0 END DESC
        ,status_id
        ,name

Upvotes: 3

Janick Bernet
Janick Bernet

Reputation: 21194

Just also add status_id, name at the end:

SELECT name, status_id FROM mytable 
ORDER BY CASE status_id WHEN 2 THEN 1 ELSE 0 END DESC, status_id, name

Upvotes: 2

D Stanley
D Stanley

Reputation: 152566

Just sort the rest by status_id and add name yo your ORDER BY:

SELECT name, status_id 
FROM mytable 
ORDER BY CASE status_id 
         WHEN 2 THEN -1 
         ELSE status_id 
         END,
         name

Upvotes: 2

Related Questions