Oracle Analytic Function: ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK()
 
The Base Query Table
SELECT ROWNUM, qt_test.bus_unit, qt_test.sales
FROM 
  (SELECT 10 as bus_unit, 2000 sales from dual UNION ALL 
   SELECT 10 as bus_unit, 2500 sales from dual UNION ALL
   SELECT 10 as bus_unit, 2500 sales from dual UNION ALL
   SELECT 20 as bus_unit, 2500 sales from dual UNION ALL
   SELECT 20 as bus_unit, 1500 sales from dual UNION ALL
   SELECT 20 as bus_unit, 1500 sales from dual UNION ALL
   SELECT 30 as bus_unit, 4500 sales from dual UNION ALL
   SELECT 30 as bus_unit, 3000 sales from dual UNION ALL
   SELECT 30 as bus_unit, 4000 sales from dual 
  ) qt_test;



Row_Number, Rank, Dense_Rank, Percent_Rank
SELECT
 qt_test.bus_unit, qt_test.sales, 
 ROW_NUMBER() OVER
  (PARTITION BY qt_test.bus_unit, qt_test.sales
   ORDER BY qt_test.bus_unit) Row_Number, 
 RANK() OVER 
  (PARTITION BY qt_test.bus_unit 
   ORDER BY qt_test.sales) rank_bu, 
 RANK() OVER 
  (ORDER BY qt_test.sales) rank_sales, 
 DENSE_RANK() OVER (ORDER BY qt_test.sales) dense_rank_sales, 
 DENSE_RANK() OVER 
  (PARTITION BY qt_test.bus_unit 
   ORDER BY qt_test.sales) dense_rank, 
 PERCENT_RANK() OVER 
  (PARTITION BY qt_test.bus_unit 
   ORDER BY qt_test.sales) pct_rank 
 FROM
 (SELECT 10 as bus_unit, 2000 sales from dual UNION ALL 
  SELECT 10 as bus_unit, 2500 sales from dual UNION ALL 
  SELECT 10 as bus_unit, 2500 sales from dual UNION ALL 
  SELECT 20 as bus_unit, 2500 sales from dual UNION ALL 
  SELECT 20 as bus_unit, 1500 sales from dual UNION ALL 
  SELECT 20 as bus_unit, 1500 sales from dual UNION ALL 
  SELECT 30 as bus_unit, 4500 sales from dual UNION ALL 
  SELECT 30 as bus_unit, 3000 sales from dual UNION ALL 
  SELECT 30 as bus_unit, 4000 sales from dual
 ) qt_test;

Back


Oracle registered trademark of Oracle Corporation.

  23707