Simple Top 6 Sales Query
-- The query below shows top 6 sales by amount only, so all four values of 
-- division 1 and two from division 2 are displayed as the top 6 sales
SELECT
  ROWNUM AS Rank, q_sum.div_id, q_sum.Region, q_sum.Sales, q_sum.regions
FROM
  (SELECT div_id, Region, sales, COUNT(*) OVER() regions
   FROM 
     (SELECT 1 Div_Id, 'North East'    region, 52550 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North East'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 25400 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'    region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central' region, 37399 sales FROM DUAL)
   ORDER BY Sales DESC) q_sum
WHERE ROWNUM <= 6;

      RANK     DIV_ID REGION             SALES    REGIONS
---------- ---------- ------------- ---------- ----------
         1          1 North East         52550          8 
         2          2 South Central      37399          8 
         3          1 North East         32970          8 
         4          2 South East         25400          8 
         5          1 North West         21175          8 
         6          1 North Central      20399          8 

 6 rows selected 

-- Refer Complete Output
 
Simple Top 3 Sales Within a Division
-- The query below shows top 3 sales by amount within a division, so three
-- values from each division are displayed to get the top 6 sales
SELECT q_sum.div_rank, q_sum.div_id, q_sum.Region, q_sum.Sales, q_sum.regions
FROM
  (SELECT div_id, Region, sales,
     RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank,
     COUNT(*) OVER() regions
   FROM 
     (SELECT 1 Div_Id, 'North East'    region, 52550 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North East'    region, 32970 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North West'    region, 21175 sales FROM DUAL UNION
      SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 18155 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South East'    region, 25400 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South West'    region, 19179 sales FROM DUAL UNION
      SELECT 2 Div_Id, 'South Central' region, 37399 sales FROM DUAL)
   ORDER BY Sales DESC) q_sum
WHERE q_sum.div_rank <= 3
ORDER BY q_sum.div_id, q_sum.div_rank;

  DIV_RANK     DIV_ID REGION             SALES    REGIONS
---------- ---------- ------------- ---------- ----------
         1          1 North East         52550          8 
         2          1 North East         32970          8 
         3          1 North West         21175          8 
         1          2 South Central      37399          8 
         2          2 South East         25400          8 
         3          2 South West         19179          8 

 6 rows selected 

-- Refer Complete Output


Oracle registered trademark of Oracle Corporation.

Last Revised On: July 27, 2014

  1307