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?
ORDER BY is a clause in SQL which is used with SELECT query to fetch the records in ascending or descending order from a table. Just like we sort the integer and the string values stored in the column of the tables, similarly, we can sort the dates stored in the SQL table's column.
Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).
If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case. ORDER BY exam_date DESC ; Note that in MySQL, NULL s are displayed first when sorting in ascending order and last when sorting in descending order.
The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
ORDER BY date = '0000-00-00' ASC, date ASC, title ASC
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With