Database Reference
In-Depth Information
The rank() function
The
rank()
function is used to get the ranks of the current row with a gap. Let's call
the
rank()
function to see what we have as results:
warehouse_db=# SELECT department_no, department_name,
department_location, rank() OVER (PARTITION BY
department_no ORDER BY department_name) FROM
warehouse_departments;
department_no | department_name | department_location | rank
---------------+-----------------+---------------------+------
10 | ACCOUNTING | NEW YORK | 1
20 | RESEARCH | DALLAS | 1
20 | RESEARCH_OPEN | PARIS | 2
30 | SALES | CHICAGO | 1
30 | SALES_INSIDE | CHICAGO | 2
30 | SALES_OUTER | CHICAGO | 3
40 | OPERATIONS | BOSTON | 1
(7 rows)
The previous statement ranks the departments based on department name. This can
be explained as follows:
• The department number
10
got
rank
1
based on department name, that is,
ACCOUNTING
• The department number
20
got
rank
1
and
2
based on department name,
that is,
RESEARCH
and
RESEARCH_OPEN
• The department number
30
got
rank
1
,
2
, and
3
based on three different
department names, that is,
SALES
,
SALES_INSIDE
, and
SALES_OUTER
• The department number
40
got
rank
1
based on department name,
that is,
OPERATIONS
Consider the following statement that ranks the departments based on the
department location. In the current case, records having the same department
location value will get the same rank.
warehouse_db=# SELECT department_no, department_name,
department_location, rank() OVER (ORDER BY
department_location) FROM warehouse_departments;
department_no | department_name | department_location | rank
---------------+-----------------+---------------------+------
40 | OPERATIONS | BOSTON | 1
30 | SALES | CHICAGO | 2
30 | SALES_OUTER | CHICAGO | 2
30 | SALES_INSIDE | CHICAGO | 2
