开发者

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

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!


SELECT DISTINCT * FROM 

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

UNION

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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜