> 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 CREATE TABLE t ( entry_id int, person_id int, primary key (entry_id) );
INSERT INTO t (entry_id, person_id) SELECT 1, 16 UNION SELECT 2, 16 UNION SELECT 3, 16 UNION SELECT 4, 16 UNION SELECT 5, 16 UNION SELECT 6, 20 UNION SELECT 7, 20 UNION SELECT 8, 20 UNION SELECT 9, 20 UNION SELECT 10, 20 ; SELECT t1.entry_id, t1.person_id FROM t t1 JOIN t t2 ON t2.person_id=t1.person_id AND t2.entry_id >= t1.entry_id GROUP BY t1.entry_id, t1.person_id HAVING count(t2.entry_id) < 4 ; --Mark _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
