What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
They all number the rows in a group, and they differ in what they do when there is a tie.
CREATE TABLE scores (
id INTEGER PRIMARY KEY,
student_id INTEGER NOT NULL,
score INTEGER NOT NULL
);
INSERT INTO scores (id, student_id, score) VALUES
(1, 1, 90), (2, 2, 90), (3, 3, 80);
So here is the difference between the three.
ROW_NUMBER always gives every row a different number. There are no gaps, so with three rows the numbers are 1, 2 and 3, even when two of the rows have the same score. The one that comes second is decided by the order in the window, so you have to add a tiebreaker if you want the same answer every time.
RANK gives the tied rows the same number, and then it skips. So the rows with a score of 90 both get the number 1, and the row with a score of 80 gets the number 3, because two rows came before it.
DENSE_RANK also gives the tied rows the same number, but it does not skip. So the rows with a score of 90 both get the number 1, and the row with a score of 80 gets the number 2.
SELECT student_id, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_num
FROM scores
ORDER BY score DESC;
student_id score row_num rank_num dense_num
1 90 1 1 1
2 90 2 1 1
3 80 3 3 2
So the rule to remember is this. ROW_NUMBER counts the rows. RANK counts the groups and leaves gaps. DENSE_RANK counts the groups and leaves no gaps.
The next two questions in this topic look at using them and at picking the right one.