CODEWITHSUNDEEP
sqlitesql-order-byconditional-statements
Francisc
Francisc

Reputation: 80455

SQlite conditional order

I have this query:

  select id,number,name 
    from objects 
order by case number when 0 then 1 else -1 end asc

The database is a database of objects with numbers. If an object doesn't have a number than its number is 0. I want to sort by number ascending, with the exception of when the number is 0, in which case, sort descending.

The query above seems to sort by ID, the default sorting order.

Thank you.

Upvotes: 3

Views: 2669

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169374

Close, but you forgot to sort by number as well:

  SELECT id,number,name 
    FROM objects 
ORDER BY CASE number WHEN 0 THEN 1 ELSE 0 END, number;

Upvotes: 5

Related Questions