RANK(): rank items, leaves a gap in the sequence when there is a tie
SQL>
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );
Table created.
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created.
SQL>
SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected.
SQL>
SQL> --RANK(): rank items, leaves a gap in the sequence when there is a tie
SQL>
SQL>
SQL> SELECT
2 prd_type_id, SUM(amount),
3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
5 FROM all_sales
6 WHERE amount IS NOT NULL
7 GROUP BY prd_type_id
8 ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
1 227276.5 1 1
2 223927.08 2 2
SQL>
SQL> drop table all_sales;
Table dropped.
SQL>
SQL>
Related examples in the same category