> 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
