> 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 per
> 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 compound index to make it faste
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,
>> [person_id] integ
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 f
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 other RDBMS added
> su
> 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 pe
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 for
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
8 matches
Mail list logo