Re: [sqlite] Last 3 items per ID

> 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

Re: [sqlite] Last 3 items per ID

> Will need to add maybe a compound index to make it faster. I bet it will be a lot slower than making several selects. But please test yourself. :) Pavel On Fri, Apr 23, 2010 at 6:46 PM, Bart Smissaert wrote: > Thanks; yes, that works. > Will need to add maybe a

Re: [sqlite] Last 3 items per ID

Thanks; yes, that works. Will need to add maybe a compound index to make it faster. RBS On Fri, Apr 23, 2010 at 11:38 PM, Igor Tandetnik wrote: > Bart Smissaert wrote: >> Simplified there is a table like this: >> >> create table xxx( >>    [entry_id] integer primary_key,

Re: [sqlite] Last 3 items per ID

Bart Smissaert 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. select * from xxx t1 where rowid in ( select rowid

Re: [sqlite] Last 3 items per ID

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 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

Re: [sqlite] Last 3 items per ID

> 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

Re: [sqlite] Last 3 items per ID

On 23 Apr 2010, at 11:03pm, Bart Smissaert wrote: > 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. I can't think of a way to do it

[sqlite] Last 3 items per ID

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