sorka <[email protected]> wrote:
> I'm sitting here banging my head trying to decide the subject for
> this post because I don't know what I'd call what I want to do :)
>
> Here's what I want to do but don't know how. The schema is simplified
> for discussion.
>
> CREATE TABLE program (time_received INTEGER, name TEXT);
> Assume indices where appropriate for performance.
>
> The table has roughly 10K entries.
>
> I'd like a select that gets all programs received within the last 10
> days and then order the results by name. However, if there are less
> than 50 results say because there aren't 50 programs that fit the
> time crieteria, I need to keep getting records, the next newest
> records until I hit 50 of them.
This should work, though probably somewhat inefficient:
select * from program where rowid in
(select rowid from program order by time_received desc limit 50
union all
select rowid from program where time_received > :cutoff_time)
order by name;
Here's another:
select * from program where time_received > min(:cutoff_time,
(select time_received from program order by time_received desc
limit 1 offset 50));
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
