Andrew Fraser DBA

Oracle select earliest and latest rows using keep group or first_value analytics

There are two ways of selecting the earliest and latest rows for each customer.

1) Keep group:

SELECT t1.customer_ref
     , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date ASC  ) AS earliest_order_amount
     , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date DESC ) AS latest_order_amount
  FROM t1
 WHERE t1.order_amount IS NOT NULL
 GROUP BY t1.customer_ref
 ORDER BY 1 ;

Max can be replaced with min or avg if required – the choice is anyway irrelevant unless two orders for the same customer have identical order_dates.

2) First_value analytics:

SELECT DISTINCT t1.customer_ref
     , FIRST_VALUE ( t1.order_amount ) IGNORE NULLS OVER ( PARTITION BY t1.customer_ref ORDER BY t1.order_date ASC  , t1.order_amount DESC ) AS earliest_order_amount
     , FIRST_VALUE ( t1.order_amount ) IGNORE NULLS OVER ( PARTITION BY t1.customer_ref ORDER BY t1.order_date DESC , t1.order_amount DESC ) AS latest_order_amount
  FROM t1
 WHERE t1.order_amount IS NOT NULL
 ORDER BY 1 ;

Again the ‘order_amount desc’ ordering is irrelevant unless two orders for the same customer have identical order_dates.

Sample data:

CREATE TABLE t1 ( customer_ref VARCHAR2(10) , order_amount NUMBER , order_date DATE ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '001' , 100 , SYSDATE - 10 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '001' , 200 , SYSDATE - 6 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '001' , 300 , SYSDATE - 4 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '001' , NULL , SYSDATE - 1 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '002' , 100 , SYSDATE - 10 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '002' , 200 , SYSDATE - 6 ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '002' , 300 , TRUNC ( SYSDATE - 4 ) ) ;
INSERT INTO t1 ( customer_ref , order_amount , order_date ) VALUES ( '002' , 250 , TRUNC ( SYSDATE - 4 ) ) ;