RANK and DENSE_RANK

Moderator: NorbertKrupa

Post Reply
User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

RANK and DENSE_RANK

Post by Jbaskin » Tue Apr 17, 2012 2:00 pm

What is the difference between these two functions?

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: RANK and DENSE_RANK

Post by id10t » Sat Apr 21, 2012 11:23 pm

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:

Code: Select all

CREATE TABLE Scores
(
    "name" varchar(80),
    scores int
);
Data:

Code: Select all

  select * from scores order by scores desc;
 name | scores 
------+--------
 Fred |     58
 Mike |     46
 Lucy |     45
 Andy |     45
 Bard |     21
Rank vs DENSE_RANK query:

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;
Result:

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)
Andy and Lucy got same rank and look at behavior of functions: RANK() - no 4th place.

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;
Result:

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

Post Reply

Return to “Vertica Analytics”