I use MariaDB and have a table where each row has a date and a score.
I want to first show the rows where the date is 3 days old or newer, sorted by the score – then show the rest (more than 3 days old) sorted by date.
Since my date is stored in unix time, it’s fairly easy to have php calculate 3 days from before now and use that as my $scoreTimeLimit variable in the below:
Here are my two queries:
SELECT * FROM myTable WHERE myDate > $scoreTimeLimit ORDER BY myPopularityScore DESC SELECT * FROM myTable WHERE myDate < $scoreTimeLimit ORDER BY myDate DESC
However, I would VERY much like to have only 1 query instead of two. Can it be done…?
Advertisement
Answer
This is a job for UNION.
SELECT * FROM ( SELECT 0 ord1, NOW() as ord2, * FROM myTable WHERE myDate > NOW() - INTERVAL 3 DAY UNION ALL SELECT 1 ord1, myDate as ord2, * FROM myTable WHERE myDate <= NOW() - INTERVAL 3 DAY ) a ORDER BY ord1, ord2 DESC, myPopularityScore
The inner query gives you a single result set with a couple of extra columns added on to help you manage your sorting.