Oracle - Dense_Rank()
-- The query below shows first four values (sales) from 
-- each location using the analytic function 
-- DENSE_RANK().

-- The DENSE_RANK() can be used to get equal sample
-- from a set of values (using four in query below
-- from each location).

SELECT
 location_code,
 sales 
FROM
(SELECT
  qtab.loc_cd  location_code,
  qtab.sales,
  DENSE_RANK() OVER
   (PARTITION BY qtab.loc_cd
        ORDER BY qtab.sales) denserank_sales
 FROM
  (SELECT 'A1' loc_cd, 111 sales FROM DUAL UNION 
   SELECT 'A1' loc_cd, 211 sales FROM DUAL UNION
   SELECT 'A1' loc_cd, 321 sales FROM DUAL UNION
   SELECT 'A1' loc_cd, 101 sales FROM DUAL UNION
   SELECT 'A1' loc_cd, 432 sales FROM DUAL UNION
   SELECT 'A1' loc_cd, 543 sales FROM DUAL
   UNION
   SELECT 'B1' loc_cd, 100 sales FROM DUAL UNION
   SELECT 'B1' loc_cd, 210 sales FROM DUAL UNION
   SELECT 'B1' loc_cd, 320 sales FROM DUAL UNION
   SELECT 'B1' loc_cd, 110 sales FROM DUAL UNION
   SELECT 'B1' loc_cd, 430 sales FROM DUAL UNION
   SELECT 'B1' loc_cd, 540 sales FROM DUAL
  ) qtab
)
WHERE denserank_sales < 5;


LOCATIOC_CODE      SALES
------------- ----------
A1                   101
A1                   111
A1                   211
A1                   321
B1                   100
B1                   110
B1                   210
B1                   320


Analytic Functions Reference

Annual Sales

Cummulative Totals

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2014

  282