Yuriy Martsynovskyy
<[EMAIL PROTECTED]> wrote:
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
You can save this solution as follows:
select * from (select * from fruits where type = 'apple' order by price
limit 2)
union all
select * from (select * from fruits where type = 'orange' order by price
limit 2)
...
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
