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 :
SELECT name, end_at
ORDER BY (end_at IS NULL OR end_at > CURDATE()), views DESC;
You almost got it. A condition in MySQL returns
1. And since
0 comes before
1 you have to either sort
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;