Didier Sampaolo Didier Sampaolo - 6 months ago 26
MySQL Question

MySQL : ORDER BY expression?

On a website, I have got a list of Items I am struggling to sort with MySQL.

Some of the items are "expired" (their end_at is in the past), and I'd like to retrieve them, but after the "active" items (their end_at can be either NULL or in the future).

I would like to be able to further sort items according to another field, let's say "views" (to get the most popular ones first)

Basically, this is what I'd like to get :


  • item 1 (active - most popular)

  • item 2 (active - 2nd most popular)

  • item 3 (active - 3rd most popular)

  • item 4 (expired)

  • item 5 (expired)

  • (and so on)



So far, this is what I have tried:

SELECT name, end_at
FROM items
ORDER BY (end_at IS NULL OR end_at > CURDATE()), views DESC;


Doesn't work : even the first returned item is expired.

Answer

You almost got it. A condition in MySQL returns 0 or 1. And since 0 comes before 1 you have to either sort desc

SELECT name,end_at FROM items 
ORDER BY (end_at IS NULL OR end_at > CURDATE()) DESC, 
         views DESC;

or negate the condition to get the desired result

SELECT name,end_at FROM items 
ORDER BY end_at IS NOT NULL AND end_at < CURDATE() ASC, 
         views DESC;