-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Thursday, March 06, 2008 7:57 AM To: [email protected] Subject: Re: [sqlite] Limiting the number of results from a query per group
"Paul Hilton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> I have a query where I wish to limit the number of rows in the result >> per group, but not limit them overall. >> >> So for example if I have a table Intable >> >> Create Table Intable (ID Integer Primary Key, Grp Integer, .....other >> stuff.....); >> >> I would like to select up to a limited number of records for each >> value of grp, say 10. >This seems to work: >select * from >(select distinct grp from Intable) t1, Intable t2 >where t2.ID in (select ID from Intable t3 where t3.grp=t1.grp limit 10); >Performance will probably be abysmal. At least create an index on Grp. >Igor Tandetnik Igor, Thanks for your reply, In the sqlite documentation under http://www.sqlite.org/lang_select.html it says: "The limit is applied to the entire query not to the individual SELECT statement to which it is attached." Why does that not apply to the 'Select *' in this query? I have done it at the moment with an insert to a table Dummygrp of each value of grp, And then a triggered query on insert into Dummygrp that writes up to 10 values to the original result table for each insert. Paul Hilton _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
