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

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

Join the forum, it's quick and easy

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

Get Answers for all your queries on IBM i (AS/400).
PMS Information Systems
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SQL Tip: Of course RANK is important- Ranking functions

Go down

SQL Tip: Of course RANK is important- Ranking functions Empty SQL Tip: Of course RANK is important- Ranking functions

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

Posts : 442
Join date : 2009-07-24

https://pmsinformationsystem.forumotion.net

Back to top Go down

Back to top

- Similar topics

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