Database Reference
In-Depth Information
The results show that:
• The rank of the irst row with department number
10
is
1/7 = 0.14
• The rank of the second and third rows with department number
20
is
3/7 =
0.42
• The rank of the fourth, ifth, and sixth rows is
6/7 = 0.85
• The rank of the seventh row is
7/7 = 1
The row_number() function
The
row_number()
function is used to get the number of the current row within its
partition, starting from
1
. Let's call the
row_number()
function using the following
statement and see the results we get:
warehouse_db=# SELECT department_no, department_name,
department_location, row_number() OVER (PARTITION
BY department_no) FROM warehouse_departments;
department_no | department_name | department_location | row_number
--------------+-----------------+---------------------+-----------
10 | ACCOUNTING | NEW YORK | 1
20 | RESEARCH_OPEN | PARIS | 1
20 | RESEARCH | DALLAS | 2
30 | SALES | CHICAGO | 1
30 | SALES_OUTER | CHICAGO | 2
30 | SALES_INSIDE | CHICAGO | 3
40 | OPERATIONS | BOSTON | 1
(7 rows)
The result of the table shows the number of rows based on the department number
partition, which can be explained in the following manner:
• The department number
10
has only one row with
row_number
1
• The department number
20
has two rows starting from number
1
up to
number
2
• The department number
30
has three rows starting from number
1
up to
number
3
• The department number
40
has only one row with
row_number
1
