-- 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
-- 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
74055