SQL Tip: Of course RANK is important- Ranking functions
Page 1 of 1
SQL Tip: Of course RANK is important- Ranking functions
SQL Tip: Of course RANK is important- Ranking functions
DB2 Ranking functions: RANK(), DENSE_RANK(), ROW_NUMBER()
Ranking functions provide the ability to define a set (using the PARTITION clause), and then rank the elements of the set with respect to an ordering. For example, suppose we have an employee table and would like to rank the employees' salaries within each department. To do this, we need a function invocation that does the following:
1) Defines the partitions (sets) to be the individual departments
2) Defines the ordering within the set to be on salary.
select empcde, empdep, empsal,
rank() over (partition by empdep
order by empsal desc nulls last) as
rank,
dense_rank() over (partition by empdep order by empsal desc
nulls last)as denserank,
row_number() over (partition by empdep order by empsal desc
nulls last)as rownumber
from empmst
DB2 Ranking functions: RANK(), DENSE_RANK(), ROW_NUMBER()
Ranking functions provide the ability to define a set (using the PARTITION clause), and then rank the elements of the set with respect to an ordering. For example, suppose we have an employee table and would like to rank the employees' salaries within each department. To do this, we need a function invocation that does the following:
1) Defines the partitions (sets) to be the individual departments
2) Defines the ordering within the set to be on salary.
select empcde, empdep, empsal,
rank() over (partition by empdep
order by empsal desc nulls last) as
rank,
dense_rank() over (partition by empdep order by empsal desc
nulls last)as denserank,
row_number() over (partition by empdep order by empsal desc
nulls last)as rownumber
from empmst
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|