PHP: Combine these two SQL queries into one

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

JavaScript
​x
 
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…?

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

JavaScript
 
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.

Advertisement

Answer