Database Reference
In-Depth Information
We used the
PARTITION BY
clause on
department_no
, so
percent_rank
will be
within the same department number. This can be explained in the following manner:
• The department number
10
has
percent_rank
0
• The department number
20
has
percent_rank
0
and
1
• The department number
30
has
percent_rank
0
,
0.5
, and
1
• The department number
40
has
percent_rank
1
, and it is calculated using
the preceding relative rank equation
The irst_value() function
The
first_value()
function is used to get a value evaluated at the irst row of the
window frame. The
first_value()
function takes the column name as the input
argument. Let's understand this with the following example:
warehouse_db=# SELECT department_no, department_name,
department_location, first_value(department_no)
OVER (ORDER BY department_no) FROM
warehouse_departments WHERE department_no > 20;
department_no | department_name | department_location | first_value
--------------+-----------------+---------------------+-----------
30 | SALES | CHICAGO | 30
30 | SALES_OUTER | CHICAGO | 30
30 | SALES_INSIDE | CHICAGO | 30
40 | OPERATIONS | BOSTON | 30
(4 rows)
In the preceding example, we get the records where
department_no
is greater than
20
, and we applied the
ORDER BY
clause on
department_no
. So in return, we will get
the list of records that have
department_no
greater than
20
. The irst department
number is
30
, so this is why the
first_value()
function returns
30
.
Let's see another example, and this time, we are sorting the records by department
location. As a result,
Boston
is the irst row. The value of
department_no
is
40
, so
this is why we get
first_value
as
40
. This is shown in the following code:
warehouse_db=# SELECT department_no, department_name,
department_location, first_value(department_no)
OVER (ORDER BY department_location) FROM
warehouse_departments WHERE department_no >20;
department_no | department_name | department_location | first_value
