RANK and DENSE_RANK
Moderator: NorbertKrupa
RANK and DENSE_RANK
What is the difference between these two functions?
Re: RANK and DENSE_RANK
If two rows receive the same rank DENSE_RANK leaves no gaps in the ranking sequence, while RANK do.
The best way to show it - example, I will skip PARTITION BY for simplicity.
Table:
Data:
Rank vs DENSE_RANK query:
Result:
Andy and Lucy got same rank and look at behavior of functions: RANK() - no 4th place.
PS:
Example with partition:
Result:
The best way to show it - example, I will skip PARTITION BY for simplicity.
Table:
Code: Select all
CREATE TABLE Scores
(
"name" varchar(80),
scores int
);
Code: Select all
select * from scores order by scores desc;
name | scores
------+--------
Fred | 58
Mike | 46
Lucy | 45
Andy | 45
Bard | 21
Code: Select all
SELECT name,
scores,
RANK() OVER (ORDER BY scores DESC) AS 'RANK()',
DENSE_RANK() OVER (ORDER BY scores DESC) AS 'DENSE_RANK()'
FROM public.Scores;
Code: Select all
name | scores | RANK() | DENSE_RANK()
------+--------+--------+--------------
Fred | 58 | 1 | 1
Mike | 46 | 2 | 2
Lucy | 45 | 3 | 3
Andy | 45 | 3 | 3
Bard | 21 | 5 | 4
(5 rows)
PS:
Example with partition:
Code: Select all
--
-- TABLE DEFINITION
--
CREATE TABLE Scores
(
sid CHAR(3),
grp CHAR(1),
score INTEGER
);
--
-- DATA
--
COPY Scores FROM STDIN DELIMITER ',';
Foo,A,5
Bar,A,2
Zip,A,2
Nec,A,1
Moo,B,3
Gen,B,7
Egg,B,7
Lug,B,2
Qux,C,2
Baz,C,3
Var,C,4
Rom,C,5
--
-- QUERY CMP
--
SELECT sid,
grp,
score,
RANK() OVER (PARTITION BY grp ORDER BY score DESC) AS 'RANK()',
DENSE_RANK() OVER (PARTITION BY grp ORDER BY score DESC) AS 'DENSE_RANK()'
FROM public.Scores;
Code: Select all
sid | grp | score | RANK() | DENSE_RANK()
-----+-----+-------+--------+--------------
Foo | A | 5 | 1 | 1
Bar | A | 2 | 2 | 2
Zip | A | 2 | 2 | 2
Nec | A | 1 | 4 | 3
Gen | B | 7 | 1 | 1
Egg | B | 7 | 1 | 1
Moo | B | 3 | 3 | 2
Lug | B | 2 | 4 | 3
Rom | C | 5 | 1 | 1
Var | C | 4 | 2 | 2
Baz | C | 3 | 3 | 3
Qux | C | 2 | 4 | 4