On Mon, May 07, 2007 at 15:38:22 +0300, Adler, Eliedaat wrote: > I need to define a query that returns only the "first' row in each group > - i.e. the row with the lowest display priority: > > - most preferably a query that doesn't require selecting RESULT_TABLE > more than once.
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; where minrow() is a user defined aggregate (that you have to write) that returns its second argument that corresponds to the minimum value of its first argument (i.e. at any moment it remembers (P, V) pair for minimum P seen so far). However, the plain SQL query should be fast enough :). -- Tomash Brechko ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
