Re: Getting the FIRST and LAST records in a group efficiently in SQL?

set autotrace on explain;

select TRAIN_I, P_VERSION_I,

       MIN(LOCATION_I) KEEP (DENSE_RANK FIRST ORDER BY ROUTE_ORDER_I ASC) FIRST_LOC,
       MIN(LOCATION_I) KEEP (DENSE_RANK LAST ORDER BY ROUTE_ORDER_I ASC) LAST_LOC
from TRAIN_ROUTE
group by TRAIN_I, P_VERSION_I;

set autotrace on explain;

8376 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=397 Card=65767 Bytes
          =1315340)

   1    0   SORT (GROUP BY) (Cost=397 Card=65767 Bytes=1315340)
   2    1     TABLE ACCESS (FULL) OF 'TRAIN_ROUTE' (Cost=128 Card=6576
          7 Bytes=1315340)

The MIN function is really misleading in this case, at least for me anyway. But this is the result, for completeness sake, that I was after.

Dean Received on Fri Dec 09 2005 - 15:11:58 CST