Thanks, I've tried that - and it works great - except that "TEST" is the result of a very complex subquery on 100,000X1000 rows "JOIN"ing TEST to TEST runs the entire subquery twice - effectively doubling the execution time (I have very limited cache space).
Any suggestions that "scans" TEST only once? -----Original Message----- From: Maulkye [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 4:35 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Help wiith SQL - first row of each group How about something like this: create table test ( Grp text, Val text, Pri integer ) insert into test values ('A', 'X', 1) insert into test values ('A', 'X', 2) insert into test values ('B', 'Y', 4) insert into test values ('B', 'Z', 2) insert into test values ('B', 'X', 8) insert into test values ('C', 'Y', 6) insert into test values ('C', 'Z', 8) insert into test values ('C', 'X', 9) insert into test values ('C', 'Y', 11) select distinct pri.* from ( select Grp, min(Pri) as 'Pri' from test group by [Grp] ) grp join ( select Grp, Val, Pri from test ) pri on grp.Grp=pri.Grp and grp.Pri=pri.Pri Grp Val Pri ---- ---- ----------- A X 1 B Z 2 C Y 6 ----- Original Message ---- From: "Adler, Eliedaat" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, May 7, 2007 8:38:22 AM Subject: [sqlite] Help wiith SQL - first row of each group Hi guys, I have a complex query result set RESULT_TABLE that returns: G V P ___________ A X 1 A X 2 B Y 4 B Z 2 B X 8 C Y 6 C Z 8 C X 9 C Y 11 G - defines groups V - some value for that specific row C - defined a display priority - i.e. I need to define a query that returns only the "first' row in each group - i.e. the row with the lowest display priority: G V P ___________ A X 1 B Z 2 C Y 6 - most preferably a query that doesn't require selecting RESULT_TABLE more than once. Thanks, Eli ************************************************************************ *********** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. ************************************************************************ *********** __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- *********************************************************************************** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. *********************************************************************************** ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
