I'm sure somebody can do better, but I I came up with this:
create table fruits (type text, variety text, price number);
create index fruit_type_price on fruits (type, price);
insert into fruits values ('apple', 'gala', 2.79);
insert into fruits values ('apple', 'fuji', 0.24);
insert into fruits values ('apple', 'limbertwig', 2.87);
insert into fruits values ('orange', 'valencia', 3.59);
insert into fruits values ('orange', 'navel', 9.36);
insert into fruits values ('pear', 'bradford', 6.05);
insert into fruits values ('pear', 'bartlett', 2.14);
insert into fruits values ('cherry', 'bing', 2.55);
insert into fruits values ('cherry', 'chelan', 6.33);
select
f.type,
f.variety,
f.price
from
fruits f
where
rowid in (select rowid from fruits where type = f.type order by price desc
limit 2)
order by
f.type asc,
f.price desc;
apple|limbertwig|2.87
apple|gala|2.79
cherry|chelan|6.33
cherry|bing|2.55
orange|navel|9.36
orange|valencia|3.59
pear|bradford|6.05
pear|bartlett|2.14
It's slow for a small result set. 100ms on my 2Ghz system under Windows. It
was over 300ms without the index.
-Clark
- Original Message
From: Yuriy Martsynovskyy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, May 11, 2007 2:44:30 PM
Subject: [sqlite] Select the top N rows from each group
I need to select top 2 (or N) most expensive fruits of each type from
this table:
+++---+
| type | variety| price |
+++---+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+++---+
The result should be this:
++--+---+
| type | variety | price |
++--+---+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| orange | navel| 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
The actual table is large and may contain millions of records.
This sample is taken from article at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
They offer this solution for MySQL as fastest:
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)
But this query will not work on SQLite as SQLite supports only one
LIMIT. Can you suggest a good way to accomplish this task on SQLite?
Correlated queries are going to be slow
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
