CODEWITHSUNDEEP
phpmysql
mask man
mask man

Reputation: 443

Mixed sorting of dates in MySQL

I have a date column which holds either a date value or null. I use that to hold a product's expiration date. I want to write a query to fetch them in this order: Not yet expired, no expiration (null), and expired.

For example, assuming today is May 15:

prd_id   prd_name   Expiry Date
-----------------
1    name1         May 16
2    name2         May 17
3    name3         May 18
4    name4         May 21
5    namex         null
6    namex         null
7    namex         null
8    namex         May 14
9    namex         May 12

(A null value denotes no expiration)

How would I do this?

Upvotes: 0

Views: 186

Answers (2)

Chris Forrence
Chris Forrence

Reputation: 10094

Essentially, you'll be joining three separate queries together:

SELECT * FROM (SELECT * FROM `product_entries` WHERE expires_on IS NOT NULL AND expires_on > CURDATE()
    ORDER BY expires_on ASC) a
  UNION ALL
SELECT * FROM (SELECT * FROM `product_entries` WHERE expires_on IS NULL) b
  UNION ALL
SELECT * FROM (SELECT * FROM `product_entries` WHERE expires_on IS NOT NULL AND expires_on <= CURDATE() 
    ORDER BY expires_on DESC) c

The first one returns those that haven't expired, the second returns those without expiration, and the third one returns expired entries.

Keep in mind that you'd want the expires_on column to be indexed. Also, as you can see in the third query, I'm counting the current date as being expired. If you want the current day to count as not being expired, then change the <= to < in the third query, and > to >= in the first query.

Another alternative would to be to use a CASE clause (if you're not concerned about the order of each entry, so long as the non-expired are at the top, the perpetual products are in the middle, and the expired entries are at the bottom)

mysql> SELECT * FROM product_entries ORDER BY CASE 
    ->     WHEN expires_on >= CURDATE() THEN 3
    ->     WHEN expires_on IS NULL THEN 2
    ->     WHEN expires_on < CURDATE() THEN 1
    -> END DESC;

+----+-------------+
| id | expire_date |
+----+-------------+
|  9 | 2015-05-11  |
|  8 | 2015-05-06  |
|  7 | 2015-05-01  |
| 10 | NULL        |
|  6 | 2015-04-26  |
|  5 | 2015-04-21  |
|  4 | 2015-04-16  |
|  3 | 2015-04-11  |
|  2 | 2015-04-06  |
|  1 | 2015-04-01  |
+----+-------------+

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try follwing Syntax:-

SELECT *
FROM YOUR_TABLE
ORDER BY CASE WHEN expire_date > CURDATE() THEN 1 END,
WHEN expire_date IS NULL THEN 2 Desc END,
WHEN expire_date < CURDATE() THEN 3 Desc END;

Upvotes: 1

Related Questions