CODEWITHSUNDEEP
phpsqlmysqlsql-order-by
OneNerd
OneNerd

Reputation: 6552

Mysql ORDER BY using date data row

I have a query something like this:

SELECT 
 title, desc, date 
FROM 
 tablename 
ORDER BY 
 date ASC, title ASC;

Works fine when the data actually has a date. Issue is, date submission is optional, so I sometimes get 0000-00-00 as a date, which has the unfortunate effect of placing all nondated rows on top.

So, I then tried this:

SELECT 
 title, desc, date 
FROM 
 tablename 
ORDER BY 
 date DESC, title ASC;

Which sort of works, but not really -- all items with dates (non 0000-00-00) get listed in descending order, followed by all items with 0000-00-00.

What I want to do is order by date ASC, title ASC, but only if the date != 0000-00-00, but if date is = 0000-00-00, then just ORDER BY title ASC on those (I think I explained that correctly).

The only ways I can think to do this are non-SQL based (either 2 queries, or, each query just populates an in-memory array, and then I sort using PHP).

Is there a SQL query that can do this?

Upvotes: 8

Views: 13258

Answers (2)

Kekoa
Kekoa

Reputation: 28240

Your 2 query solution is a good one, you can do it all in SQL using the UNION command.

The first query will be for dates that are non-zero, then UNION in the query for dates that are zero.

Edit: Something like:

SELECT * FROM tbl 
   WHERE DATE != '0000-00-00' 
   ORDER BY date ASC 
UNION SELECT * FROM tbl
   WHERE DATE = '0000-00-00'
   ORDER BY title ASC

This may not be very useful in this instance, but for complex queries, UNION can come in handy.

Upvotes: 4

Frank Farmer
Frank Farmer

Reputation: 39356

ORDER BY date = '0000-00-00' ASC, date ASC, title ASC

Upvotes: 9

Related Questions