CODEWITHSUNDEEP
mysql
user925885
user925885

Reputation:

Order by largest number descending, but with null ranking higher

I have this query:

SELECT user.user_id,
       user.user_name,
       user.user_hash,
       suspension.suspension_expireson
FROM   user
       LEFT JOIN suspension
              ON suspension.suspension_user = user.user_id
WHERE  user.user_name = "tester"
ORDER  BY suspension.suspension_expireson DESC
LIMIT  1;

It orders the suspension_expireson so that the highest number (it's unixtime, so the furthest in the future) comes up first, but I want NULL to be ranked higher than any number. So if there's a row with the value NULL in suspension_expireson, I want that one.

How should I modify the query to do that?

Thanks in advance!

Upvotes: 0

Views: 217

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270697

Use a CASE statement in the ORDER BY to give a lower constant value to nulls 0 than other rows, which get a 1. The 0 sorts ahead of the 1, then the rest (1) are subordered by suspension_expireson DESC. 

SELECT
  user.user_id, 
  user.user_name, 
  user.user_hash, 
  suspension.suspension_expireson 
FROM 
  user 
  LEFT JOIN suspension ON suspension.suspension_user = user.user_id 
WHERE user.user_name = "Tester" 
ORDER BY
  CASE WHEN suspension_expireson IS NULL THEN 0 ELSE 1 END,
  suspension.suspension_expireson DESC
LIMIT 1;

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

This will sort all the NULL values first, and will then sort non-NULL values in descending order.

ORDER BY case when suspension.suspension_expireson is null then 0 else 1 end, suspension.suspension_expireson DESC LIMIT 1;

Upvotes: 0

Related Questions