CODEWITHSUNDEEP
mysqlsqldatabasedatetime
user3657547
user3657547

Reputation: 31

MySQL Query ORDER BY DateTime Issue

I'm trying to order the query by the latest update datetime greater than now ascending then less than now descending. This is my desired output:

2014-06-01 00:00:00
2014-06-04 00:00:00
2014-06-05 00:00:00
2014-06-06 00:00:00
2014-05-19 00:00:00
2014-05-15 00:00:00
0000-00-00 00:00:00

But Im getting this instead:

2014-06-01 00:00:00
2014-06-04 00:00:00
2014-06-05 00:00:00
2014-06-06 00:00:00
0000-00-00 00:00:00
2014-05-15 00:00:00
2014-05-19 00:00:00

The Query I'm using is this:

SELECT lastupdated FROM users ORDER BY lastupdated > NOW() DESC, lastupdated ASC;

Upvotes: 3

Views: 1258

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

What is the problem? The date 0000-00-00 is in the past, so it is listed first for that group. If you want it last overall, put it there explicitly:

order by (case when lastupdated = '0000-00-00 00:00:00' then 1
               when lastupdate > now() then 2
               else 3
          end),
         lastupdated asc;

If you want the ordering to be descending for the last group, then do:

order by (lastupdate > now()) desc,
         (case when lastupdate > now() then lastupdate end) asc,
         lastupdate desc

Upvotes: 1

barbiepylon
barbiepylon

Reputation: 911

Explicitly sort timestamps < NOW() similar to how you're sorting > NOW()

SELECT lastupdated FROM users ORDER BY lastupdated > NOW() DESC, lastupdated <= NOW() ASC;

Upvotes: -1

chresse
chresse

Reputation: 5805

you can do it with UNION:

SELECT lastupdated 
FROM users 
WHERE lastupdated > NOW()
ORDER BY lastupdated  DESC

UNION

SELECT lastupdated 
FROM users 
WHERE lastupdated <= NOW()
ORDER BY lastupdated ASC;

Upvotes: 2

Related Questions