On Mon, May 07, 2007 at 18:10:29 +0400, Tomash Brechko wrote: > The solution with two table scans might be > > SELECT * FROM RESULT_TABLE a > WHERE P = (SELECT MIN(P) FROM RESULT_TABLE b WHERE b.G = a.G); > > You may also try to play with indexes G, P. > > It seems to be impossible to do only one table scan with plain SQL. > However, you may trick it: > > SELECT G, minrow(P, V), min(P) FROM RESULT_TABLE GROUP BY G;
After considering a bit more, no, it won't be faster. I was thinking that there are several aggregates in progress, and the row is pushed into aggregate function of the corresponding bucket right away. Perhaps this is how SQLite 2.8.x has worked (based on VDBE Tutorial description). I guess there were no user defined aggregates back then. But with SQLite 3.x there is only one user data pointer per user defined aggregate function, so no parallel aggregate execution is possible. Thus the algorithm of GROUP BY is effectively two-scan: first order by GROUP BY columns, then process all ordered rows with AggStep, calling AggFinal when key changes. So the first query with the index on G (maybe on (G, P)) is probably near optimal. -- Tomash Brechko ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
