CODEWITHSUNDEEP
mysql
Patrick Moore
Patrick Moore

Reputation: 13344

MySQL sort conditionally on two joined columns

I have a MySQL table structured with two date fields, like so:

uid int auto-increment
sponsor_name varchar
date_sent datetime
date_resent datetime

Let's assume data like:

(1, 'patrick', '2014-03-01 12:30:00', '0000-00-00 00:00:00' )
(2, 'patrick', '2014-03-05 14:30:00', '2014-03-18 00:30:00' )
(3, 'patrick', '2014-03-08 16:30:00', '2014-03-24 00:00:00' )
(4, 'patrick', '2014-03-20 12:30:00', '0000-00-00 00:00:00' )

When I run SELECT * FROM table ORDER BY date_resent DESC I see data returned as expected with the rows having some date_resent first, followed by those without:

(3, 'patrick', '2014-03-08 16:30:00', '2014-03-24 00:00:00' )
(2, 'patrick', '2014-03-05 14:30:00', '2014-03-18 00:30:00' )
(1, 'patrick', '2014-03-01 12:30:00', '0000-00-00 00:00:00' )
(4, 'patrick', '2014-03-20 12:30:00', '0000-00-00 00:00:00' )

But what I want is to use some combination of both columns date_sent and date_resent, so that in the case date_resent is empty ('000-00-00 00:00:00') the date_sent is substituted in the ordering.

The desired result would be data:

(3, 'patrick', '2014-03-08 16:30:00', '2014-03-24 00:00:00' )
(4, 'patrick', '2014-03-20 12:30:00', '0000-00-00 00:00:00' )
(2, 'patrick', '2014-03-05 14:30:00', '2014-03-18 00:30:00' )
(1, 'patrick', '2014-03-01 12:30:00', '0000-00-00 00:00:00' )

Upvotes: 0

Views: 27

Answers (1)

Jakub Matczak
Jakub Matczak

Reputation: 15656

What about:

SELECT * FROM table
ORDER BY IF(data_resent = '0000-00-00 00:00:00', data_sent, data_resent) DESC

Or if data_resent when not empty is always greater then data_sent (I guess it is), then you could try something like:

SELECT * FROM table
ORDER BY GREATEST(data_sent, data_resent) DESC

Upvotes: 2

Related Questions