Mysql query sorting on two date fields

Question

I am trying to sort data on two date fields, start and end. But there is a peculiar condition. It should sort on both the fields parallely.

My users table

id | start                  | end
---------------------------------------------------
1  | 2015-02-24 00:00:00    | 2015-02-18 00:00:00
---------------------------------------------------
2  | 2015-02-24 00:00:00    | 2015-02-03 00:00:00
---------------------------------------------------
3  | 2015-02-25 00:00:00    | NULL
---------------------------------------------------
4  | 2015-02-23 00:00:00    | NULL
---------------------------------------------------

Expected Output:

id | start                  | end
---------------------------------------------------
3  | 2015-02-25 00:00:00    | NULL
---------------------------------------------------
4  | 2015-02-23 00:00:00    | NULL
---------------------------------------------------
1  | 2015-02-24 00:00:00    | 2015-02-18 00:00:00
---------------------------------------------------
2  | 2015-02-24 00:00:00    | 2015-02-03 00:00:00
---------------------------------------------------

As you can see the row with id 1 comes in the 3rd place because the value of end date exists and it is smaller than the previous record start date.

The table should sort on both the fields at the same time. And it should give a higher priority to the end field. But if the end field is null, it should carry on the sorting on START field.

I have tried the below queries:

SELECT * FROM `users` order by end DESC, start DESC 
SELECT * FROM `users` order by start DESC,least(start,end) DESC

The above queries gives me a result which is something like this but as you can see it didn't consider the end date. I mean it did, but thats not how i want:

id | start                  | end
---------------------------------------------------
3  | 2015-02-25 00:00:00    | NULL
---------------------------------------------------
1  | 2015-02-24 00:00:00    | 2015-02-18 00:00:00
---------------------------------------------------
2  | 2015-02-24 00:00:00    | 2015-02-03 00:00:00
---------------------------------------------------
4  | 2015-02-23 00:00:00    | NULL
---------------------------------------------------

Below is the create table and sample data if you want to execute and try.

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) NOT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `start`, `end`) VALUES
(1, '2015-02-24 00:00:00', '2015-02-03 00:00:00'),
(2, '2015-02-24 00:00:00', '2015-02-18 00:00:00'),
(3, '2015-02-25 00:00:00', NULL),
(4, '2015-02-23 00:00:00', NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

do you just want this: `SELECT * FROM users order by end ASC, start DESC; ` ? or pls explain better — guido, Feb 25 '15 at 04:21
You need to provide more detail on your sorting requirement. When do we look at `start`? When do we look at `end`? How do we treat `NULL`? What was the problem with your attempt? — shmosel, Feb 25 '15 at 04:30
In your expected output you have put "NULL" values earlier because they are lower but you have put 2015-02-03 *after* 2015-02-18. Are you trying to do an ascending or a descending sort on end-date? — Peter Bowers, Feb 25 '15 at 08:27
Can you provide a 'real-world' example of something that ends before it starts? And define 'previous record' — Strawberry, Feb 25 '15 at 08:45

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

OK, I think I understand what you're trying to do - the question was kind of confusing. Try this:

SELECT * FROM `users` order by IF(end IS NULL, start, end) DESC

See also Can you add an if statement in ORDER BY? for more details

edited May 23 '17 at 10:24

Community

1
1

answered Feb 25 '15 at 08:36

Peter Bowers

3,063
1
10
18

That worked perfect as well. But i had to tick Justin's answer as he was ahead by an hour. Thanks for the alternate. – Abhishek Saha Feb 25 '15 at 13:44

score 0 · Accepted Answer · answered Feb 25 '15 at 08:44

Query: SQLFIDDLEExample

SELECT * 
FROM `users` 
order by COALESCE(end,start)  DESC, start DESC

Result:

| ID |                           START |                 END |
|----|---------------------------------|---------------------|
|  3 | February, 25 2015 00:00:00+0000 |              (null) |
|  4 | February, 23 2015 00:00:00+0000 |              (null) |
|  1 | February, 24 2015 00:00:00+0000 | 2015-02-18 00:00:00 |
|  2 | February, 24 2015 00:00:00+0000 | 2015-02-03 00:00:00 |

Mysql query sorting on two date fields

2 Answers2