PMS Information Systems
Welcome to PMS Information Systems - IBM i (AS/400) Forum !!!

Get Answers for all your queries on IBM i (AS/400).

SQL Tip: Of course RANK is important- Ranking functions

View previous topic View next topic Go down

SQL Tip: Of course RANK is important- Ranking functions

Post  maran on Sat May 21, 2016 10:26 pm

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  
avatar
maran
Admin

Posts : 442
Join date : 2009-07-24

View user profile http://pmsinformationsystem.forumotion.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum