SQL select at least X rows that are in a given time

Question

hello, i am looking for an SQL query, that selects exactly rows ordered by date from a table. for this there is a column that contains a timestamp.

and here comes the tricky part: it should not select rows that are older than a certain time, but it should still select at least X rows. so if in the given time there are not more than X rows, it should move the time back until it has at least X rows.

thanks!

Answer 1

SELECT DISTINCT * FROM 

    (SELECT TOP 100 *
    FROM MyTable
    ORDER BY dateColumn DESC) A

UNION

    (SELECT *
    FROM MyTable
    WHERE dateColumn > '20090101')

Answer 2

I think you could achieve this by sorting by two columns. The sort column would be an expression that says whether the row is in the specified date range. The second sort column would be the date itself

something like

select top x *
from the_table
order by 
    case when the_table.the_date between '1-jan-2009' and '31-jan-2009' then 0 else 1 end,
    the_table.the_date desc