RANK
Let's assume we want to assign a rank to students in a college department wise based on total_marks. Below is the example with the results
when a RANK() function is used.
From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is not consecutive. Now lets see how using a DENSE_RANK() will change these results.
DENSE_RANK
From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is consecutive.
Let's assume we want to assign a rank to students in a college department wise based on total_marks. Below is the example with the results
when a RANK() function is used.
SELECT studentno,
deptno,
total_marks,
RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"
FROM students_table;
STUDENT_NO DEPTNO total_marks rank
---------- -------- ---------- -------
1001 100 1300 1
1005 100 1300 1
1006 100 1400 3
1009 200 1100 1
1002 200 1150 2
1004 200 1150 2
1003 200 1200 4
From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is not consecutive. Now lets see how using a DENSE_RANK() will change these results.
DENSE_RANK
SELECT studentno,
deptno,
total_marks,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"
FROM student_table;
STUDENT_NO DEPTNO total_marks rank
---------- ---------- ---------- ----------
1001 100 1300 1
1005 100 1300 1
1006 100 1400 2
1009 200 1100 1
1002 200 1150 2
1004 200 1150 2
1003 200 1200 3
From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is consecutive.