Alvin Alvin - 4 months ago 11
MySQL Question

How to make order by field work only when it meets specific condition?

I want to order mysql query results by field topped_time only when it's earlier than now.For example, if topped_time is 2016-7-6, it should be taken into consideration, but if topped_time is 2016-7-16, it should be ignored.

I tried

SELECT * FROM `article` ORDER BY IF(`topped_time` < CURRENT_TIME(), '`topped_time` DESC', ''), `published_time` DESC


and

SELECT * FROM `article` ORDER BY CASE WHEN `topped_time=` < CURRENT_TIME() THEN `topped_time` END, `published_time` DESC


still ordering by topped_time even when it's later than now.

Here is the table:

CREATE TABLE `article` (
`id` bigint(20) UNSIGNED NOT NULL,
`published_time` datetime DEFAULT '0000-00-00 00:00:00',
`topped_time` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `article` (`id`, `published_time`, `topped_time`) VALUES
(1, '2016-07-05 22:01:14', '0000-00-00 00:00:00'),
(2, '2016-07-05 22:01:23', '0000-00-00 00:00:00'),
(3, '2016-07-05 22:01:25', '2016-07-07 00:00:00'),
(4, '2016-07-05 22:01:27', '0000-00-00 00:00:00'),
(5, '2016-07-05 22:01:29', '0000-00-00 00:00:00');


enter image description here

The correct order from this screenshot should be id:4,5,3,2,1, because the topped_time of id 4 is 2016-07-06 00:00:00, which is earlier than now, and should be the first one. While the topped_time of id 3 is 2016-07-07 00:00:00, and is later than now, which should be ignored.

what's the correct query or is it impossible?

Answer

I guess this is your choice:

SELECT * FROM `article`
ORDER BY 
    IF (`topped_time` < NOW(), `topped_time`, '0000-00-00 00:00:00') DESC, 
    `published_time`  DESC

+----+---------------------+---------------------+
| id | published_time      | topped_time         |
+----+---------------------+---------------------+
|  4 | 2016-07-05 22:01:27 | 2016-07-06 00:00:00 |
|  6 | 2016-07-05 22:01:28 | 2016-05-06 00:00:00 |
|  5 | 2016-07-05 22:01:29 | 0000-00-00 00:00:00 |
|  3 | 2016-07-05 22:01:25 | 2016-07-17 00:00:00 |
|  2 | 2016-07-05 22:01:23 | 0000-00-00 00:00:00 |
|  1 | 2016-07-05 22:01:14 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+

UPDATE

As you may notice I added additional row to prove that your solution is wrong

SELECT * FROM article 
ORDER BY topped_time < CURRENT_TIME() AND 
         topped_time DESC, published_time DESC;

+----+---------------------+---------------------+
| id | published_time      | topped_time         |
+----+---------------------+---------------------+
|  6 | 2016-07-05 22:01:28 | 2016-05-06 00:00:00 |
|  4 | 2016-07-05 22:01:27 | 2016-07-06 00:00:00 |
|  5 | 2016-07-05 22:01:29 | 0000-00-00 00:00:00 |
|  3 | 2016-07-05 22:01:25 | 2016-07-17 00:00:00 |
|  2 | 2016-07-05 22:01:23 | 0000-00-00 00:00:00 |
|  1 | 2016-07-05 22:01:14 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+
Comments