CODEWITHSUNDEEP
mysqlsql-order-by
Didier Sampaolo
Didier Sampaolo

Reputation: 2823

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 :

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.

Upvotes: 0

Views: 794

Answers (2)

juergen d
juergen d

Reputation: 204756

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;

Upvotes: 1

Didier Sampaolo
Didier Sampaolo

Reputation: 2823

I just found a way :

SELECT *
FROM items 
ORDER BY CASE
WHEN (end_at IS NULL OR end_at > CURDATE()) 
    THEN 0 
    ELSE 1 
END, views DESC;

The CASE WHEN affects a value on each row, according to an expression. The sort is done as usual, using this computed value.

Upvotes: 0

Related Questions