Search code examples
mysqlsqlsql-order-bycase-when

SQL ORDER conditionally by column plus secondary column

I need to do an sql order by which covers a few cases...

I first need to order by a column expiry (epoch string), where this is greater than now or it is empty (non-expiring).

So expired records (those where expiry is less than now) will always come after the non-expired records.

Then within each of these cases, there needs to be a secondary search which is alphabetical.

tTABLE id name expiry active

My attempt so far:

select * from `tTABLE` WHERE `active`='1' 
ORDER BY 
CASE WHEN `expiry` > 1571410101 THEN 1
CASE WHEN `expiry` = '' THEN 2 ELSE 3 END,`expiry`
DESC
Solution

  • I think you want:

    SELECT t.*
FROM `tTABLE` t
WHERE  active = 1  
ORDER BY (CASE WHEN expiry > unixtime() 
               THEN 1   -- explicit future expires first
               WHEN expiry IS NULL
               THEN 2   -- no expires second
               ELSE 3
          END),
         name;