Yes, that is the best I could come up with. Thanks for confirming that there is nothing better.
RBS On Fri, Apr 23, 2010 at 11:18 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> Can I do this in SQL or do I need to do this in my application? > > You cannot do that with pure standard SQL. Some other RDBMS added > support for such things into their SQL dialect, but not SQLite. You > can pretty easily do this in your application by querying all > person_id first: > > select distinct person_id from xxx; > > Then for each person_id you can query what you need: > > select entry_id from xxx > where person_id = ? > order by entry_id desc > limit 3; > > > Pavel > > On Fri, Apr 23, 2010 at 6:03 PM, Bart Smissaert > <bart.smissa...@gmail.com> wrote: >> Simplified there is a table like this: >> >> create table xxx( >> [entry_id] integer primary_key, >> [person_id] integer) >> >> Now I need to retrieve the rows with the 3 highest entry_id numbers >> for each person_id. >> >> so for example (in reality entry_id can have gaps): >> >> entry_id person_id >> ----------------------------- >> 1 16 >> 2 16 >> 3 16 >> 4 16 >> 5 16 >> 6 20 >> 7 20 >> 8 20 >> 9 20 >> 10 20 >> >> I would need to produce: >> >> entry_id person_id >> ----------------------------- >> 3 16 >> 4 16 >> 5 16 >> 8 20 >> 9 20 >> 10 20 >> >> Can I do this in SQL or do I need to do this in my application? >> It doesn't have to be done in one statement. >> >> >> RBS >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
