-- Use of First and Last In Aggregation:
-- The maximum value for division 10 is 2170, which
-- corresponds to commission percentage of 0.45.
-- Similarly the minimum value for division 10 is 
-- 1950 that corresponds to commission percent is
-- 0.17, the lowest value.

WITH q_div AS
(SELECT 10 div_id, 2170 sales, 0.45 com_pct FROM DUAL UNION
 SELECT 10 div_id, 2350 sales, 0.27 com_pct FROM DUAL UNION
 SELECT 10 div_id, 1950 sales, 0.17 com_pct FROM DUAL UNION
 SELECT 10 div_id, 1910 sales, 0.23 com_pct FROM DUAL UNION
 SELECT 20 div_id, 1770 sales, 0.29 com_pct FROM DUAL UNION
 SELECT 20 div_id, 1920 sales, 0.41 com_pct FROM DUAL UNION
 SELECT 20 div_id, 2050 sales, 0.31 com_pct FROM DUAL UNION
 SELECT 20 div_id, 2910 sales, 0.23 com_pct FROM DUAL)
SELECT  div_id,
  MAX(sales) KEEP (DENSE_RANK LAST  ORDER BY com_pct)
   "BEST  Sales-Com%",
  MIN(sales) KEEP (DENSE_RANK FIRST ORDER BY com_pct)
   "WORST Sales-Com%"
FROM q_div
GROUP BY div_id;

--Use of First and Last As An Analytic Function:
-- The best and worst values are the same as shown in 
-- aggregate query, and all eight rows are displayed.

WITH q_div AS
(SELECT 10 div_id, 2170 sales, 0.45 com_pct FROM DUAL UNION
 SELECT 10 div_id, 2350 sales, 0.27 com_pct FROM DUAL UNION
 SELECT 10 div_id, 1950 sales, 0.17 com_pct FROM DUAL UNION
 SELECT 10 div_id, 1910 sales, 0.23 com_pct FROM DUAL UNION
 SELECT 20 div_id, 1770 sales, 0.29 com_pct FROM DUAL UNION
 SELECT 20 div_id, 1920 sales, 0.41 com_pct FROM DUAL UNION
 SELECT 20 div_id, 2050 sales, 0.31 com_pct FROM DUAL UNION
 SELECT 20 div_id, 2910 sales, 0.23 com_pct FROM DUAL)
SELECT  div_id, sales, com_pct,
  MAX(sales) KEEP (DENSE_RANK LAST  ORDER BY com_pct)
  OVER (PARTITION BY div_id) "BEST  Sales-Com%",
  MIN(sales) KEEP (DENSE_RANK FIRST ORDER BY com_pct) 
  OVER (PARTITION BY div_id) "WORST Sales-Com%"
FROM q_div;