SQL select until full sort of problem

Question

Hi

I am trying to write some SQL that pulls from several tables using unions, where the date on the rows is less than a hour.

But if the total rows from the SQL query doesn't add up to about 20 then it should change the where to the date being less than two hours and so forth until 20 rows retrieved.

Is this possible to do solely in SQL? I have a way of implementing it using PHP as well, but would prefer to have it all done in SQL.

Answer 1

you should select the first 20 when ordered by date desc ..

so no need calculate the ranges..

Sql Server

SELECT TOP 20 ..

other DBs

SELECT ... LIMIT 0, 20

Answer 2

This probably won't work for all DB servers, but you should be able to do something like

SELECT TOP 20 * 
FROM (SELECT your columns from table1 UNION SELECT your columns from table2) temp
ORDER BY [the date column] DESC

Selecting from a subquery slows things down a bit, though, and you'd have to avoid the WHERE clauses in the subquery (or at least, set them far enough back that they'd always return at least 20 rows) in order for things to work.

There's probably something very similar that'll work with MySQL, as it doesn't do TOP xxx. You should be able to say LIMIT 20 at the end instead, but i'm not sure whether MySQL will let you select from subqueries like that. I remember having problems with it in the past, but frankly, MySQL was a toy til v5.0.