-- To get the top 6 regions with high sales (Top-N query) can be obtained -- as shown below using the syntax -- FETCH FIRST 6 ROWS ONLY. 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 FETCH FIRST 6 ROWS ONLY; SELECT div_id, RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank, region, sales, COUNT(*) OVER() regions FROM (SELECT 1 Div_Id, 'North East-1' region, 52550 sales FROM DUAL UNION SELECT 1 Div_Id, 'North East-2' region, 32970 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-1' region, 21175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-2' region, 22175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-1' region, 18155 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-2' region, 20399 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 UNION SELECT 3 Div_Id, 'Central' region, 20399 sales FROM DUAL ) ORDER BY Sales DESC ; -- Complete data from above query -- Output => SELECT div_id, RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank, region, sales, COUNT(*) OVER() regions FROM (SELECT 1 Div_Id, 'North East-1' region, 52550 sales FROM DUAL UNION SELECT 1 Div_Id, 'North East-2' region, 32970 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-1' region, 21175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-2' region, 22175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-1' region, 18155 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-2' region, 20399 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 UNION SELECT 3 Div_Id, 'Central' region, 20399 sales FROM DUAL ) ORDER BY Sales DESC FETCH FIRST 6 ROWS WITH TIES; -- First 6 rows with ties results in 8 rows since two others have same value (20399) -- Output => SELECT div_id, RANK() OVER(PARTITION BY div_id ORDER BY sales DESC) div_rank, region, sales, COUNT(*) OVER() regions FROM (SELECT 1 Div_Id, 'North East-1' region, 52550 sales FROM DUAL UNION SELECT 1 Div_Id, 'North East-2' region, 32970 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-1' region, 21175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North West-2' region, 22175 sales FROM DUAL UNION SELECT 1 Div_Id, 'North Central' region, 20399 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-1' region, 18155 sales FROM DUAL UNION SELECT 2 Div_Id, 'South East-2' region, 20399 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 UNION SELECT 3 Div_Id, 'Central' region, 20399 sales FROM DUAL ) ORDER BY Sales DESC FETCH FIRST 25 PERCENT ROWS ONLY; -- The top 25% gets rounded to 30% and top three rows are output -- Output =>
9880