RANK
Examples
select
id,
salary,
rank() over (order by salary)
from employee
order by salary;
id salary rank -- ------ ---- 3 8.00 1 4 9.00 2 1 10.00 3 5 10.00 3 2 12.00 5
RANK
Examplesselect
id,
salary,
rank() over (order by salary)
from employee
order by salary;
id salary rank -- ------ ---- 3 8.00 1 4 9.00 2 1 10.00 3 5 10.00 3 2 12.00 5
ROW_NUMBER()
Sequential row number in the partition
BIGINT
ROW_NUMBER () OVER <window_name_or_spec>
Returns the sequential row number in the partition, where 1
is the first row in each of the partitions.
ROW_NUMBER
Examplesselect
id,
salary,
row_number() over (order by salary)
from employee
order by salary;
id salary rank -- ------ ---- 3 8.00 1 4 9.00 2 1 10.00 3 5 10.00 4 2 12.00 5
The navigational functions get the simple (non-aggregated) value of an expression from another row of the query, within the same partition.
Important
|
This is likely to produce strange or unexpected results for |
select
id,
salary,
first_value(salary) over (order by salary),
last_value(salary) over (order by salary),
nth_value(salary, 2) over (order by salary),
lag(salary) over (order by salary),
lead(salary) over (order by salary)
from employee
order by salary;
id salary first_value last_value nth_value lag lead -- ------ ----------- ---------- --------- ------ ------ 3 8.00 8.00 8.00 <null> <null> 9.00 4 9.00 8.00 9.00 9.00 8.00 10.00 1 10.00 8.00 10.00 9.00 9.00 10.00 5 10.00 8.00 10.00 9.00 10.00 12.00 2 12.00 8.00 12.00 9.00 10.00 <null>