Database Reference
In-Depth Information
20 | RESEARCH | DALLAS | 5
10 | ACCOUNTING | NEW YORK | 6
20 | RESEARCH_OPEN | PARIS | 7
(7 rows)
The dense_rank() function
The
dense_rank()
function is used to get the rank of the current row without any
gaps. Rows with equal values for the ranking criteria receive the same rank. The
dense_rank()
function differs from the
rank()
function in one respect; if there is a
tie between two or more rows, there is no gap in the sequence of the ranked values.
Let's call the
dense_rank()
function to see the results:
warehouse_db=# SELECT department_no, department_name,
department_location, dense_rank() OVER (order by
department_no) FROM warehouse_departments;
department_no | department_name | department_location | dense_rank
--------------+-----------------+---------------------+-----------
10 | ACCOUNTING | NEW YORK | 1
20 | RESEARCH_OPEN | PARIS | 2
20 | RESEARCH | DALLAS | 2
30 | SALES | CHICAGO | 3
30 | SALES_OUTER | CHICAGO | 3
30 | SALES_INSIDE | CHICAGO | 3
40 | OPERATIONS | BOSTON | 4
(7 rows)
We have four different types of department number. So, the maximum rank is
4
and
the results show that:
• The row with department number
10
has
dense_rank
1
• The
dense_rank
value of
row_number
2
and
3
with department number
20
is
2
• The
dense_rank
value of
row_number
4
,
5
, and
6
with department number
30
is
3
• The
dense_rank
value of
row_number
7
with department number
40
is
4
