Using A Pl-sql Procedure Or Cursor To Select Top 3 Rank

Can someone please tell how can I get the results as below. Using dense_rank function where rank <=2 will give me top 2 offers. I am also looking to get 'total_offer' which sh

Solution 1:

Try this....

select*from (
select customer,make,zipcode,offer, dense_rank() over (PARTITIONby customer orderby customer,make, zipcode,offer desc) Rank from tablename)
where Rank <4;

Solution 2:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATETABLE TEST ( customer, make, zipcode, offer ) ASSELECT'mark',  'focus',    101,  250FROM DUAL
UNIONALLSELECT'mark',  'focus',    101, 2500FROM DUAL
UNIONALLSELECT'mark',  'focus',    101, 1000FROM DUAL
UNIONALLSELECT'mark',  'focus',    101, 1500FROM DUAL
UNIONALLSELECT'henry', '520i',   21405,  500FROM DUAL
UNIONALLSELECT'henry', '520i',   21405,  100FROM DUAL
UNIONALLSELECT'henry', '520i',   21405,  750FROM DUAL
UNIONALLSELECT'henry', '520i',   21405,  100FROM DUAL
UNIONALLSELECT'mark',  'taurus', 48360,  250FROM DUAL
UNIONALLSELECT'mark',  'mustang',  730,  500FROM DUAL
UNIONALLSELECT'mark',  'mustang',  730, 1000FROM DUAL
UNIONALLSELECT'mark',  'mustang',  730, 1250FROM DUAL;

Query 1 - If you want at most 3 rows per group::

WITH ranks AS (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITIONBY CUSTOMER, MAKE, ZIPCODE ORDERBY OFFER DESC ) AS RANK
  FROM   TEST t
)
SELECT*FROM   RANKS
WHERE  RANK <=3

Results:

| CUSTOMER |    MAKE | ZIPCODE | OFFER | RANK |
|----------|---------|---------|-------|------|
|    henry |    520i |   21405 |   750 |    1 |
|    henry |    520i |   21405 |   500 |    2 |
|    henry |    520i |   21405 |   100 |    3 |
|     mark |   focus |     101 |  2500 |    1 |
|     mark |   focus |     101 |  1500 |    2 |
|     mark |   focus |     101 |  1000 |    3 |
|     mark | mustang |     730 |  1250 |    1 |
|     mark | mustang |     730 |  1000 |    2 |
|     mark | mustang |     730 |   500 |    3 |
|     mark |  taurus |   48360 |   250 |    1 |

Query 2 - If you want the top 3 ranks including ties:

WITH ranks AS (
  SELECT t.*,
         DENSE_RANK() OVER ( PARTITIONBY CUSTOMER, MAKE, ZIPCODE ORDERBY OFFER DESC ) AS RANK
  FROM   TEST t
)
SELECT*FROM   RANKS
WHERE  RANK <=3

Results:

| CUSTOMER |    MAKE | ZIPCODE | OFFER | RANK |
|----------|---------|---------|-------|------|
|    henry |    520i |   21405 |   750 |    1 |
|    henry |    520i |   21405 |   500 |    2 |
|    henry |    520i |   21405 |   100 |    3 |
|    henry |    520i |   21405 |   100 |    3 |
|     mark |   focus |     101 |  2500 |    1 |
|     mark |   focus |     101 |  1500 |    2 |
|     mark |   focus |     101 |  1000 |    3 |
|     mark | mustang |     730 |  1250 |    1 |
|     mark | mustang |     730 |  1000 |    2 |
|     mark | mustang |     730 |   500 |    3 |
|     mark |  taurus |   48360 |   250 |    1 |