Ranking and Numbering with SQL

Beginning in DB2 9, IBM introduced support for On-Line Analytical Processing (OLAP) built-in functions.  The first three functions introduced may be used to rank and number rows in a result table.  This article will explore these three functions which are available on both DB2 for z/OS and DB2 for LUW as well as many other database platforms.

Rank Function

Let’s begin with a simple example:

SELECT LASTNAME, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
ORDER BY SALARY DESC

1.jpg

The OVER syntax here may contain either an ordering clause, a partitioning (windowing) clause, or both.  The example above shows only an ordering clause.  This ORDER BY controls the order of the ranking, NOT necessarily the order of the final result.  Where there is a tie, all rows with the value receive the same rank.  In this example since both HAAS and MOON have the same salary the value of “2” is skipped.

The result set may be ordered using a final ORDER BY that need not be the same as the order of the ranking.  When this is done, two sorts may be needed to achieve the desired result.

SELECT LASTNAME, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
ORDER BY LASTNAME

2.jpg

Dense Rank

DENSE_RANK works like RANK, except no numbers are “skipped” when there is a tie.  In this example, LUCCHESI receives a rank of 2 even though he is the 3rd highest salary.

SELECT LASTNAME, SALARY,
  DENSE_RANK() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
ORDER BY SALARY DESC

4.jpg

Row Number

ROW_NUMBER may be used to simply assign a unique number to each row of a result.  This operation may also be ordered, although this is not required. 

SELECT LASTNAME, SALARY,
  ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS R
FROM EMP
ORDER BY SALARY DESC

5.jpg

If you don’t care what order the numbers are assigned, you can leave the order blank.

SELECT LASTNAME, SALARY,
  ROW_NUMBER() OVER () AS R
FROM EMP
ORDER BY SALARY DESC 

When this is done, the order will depend on the specific access path DB2 chooses.  If you place an ORDER BY over the entire result, the row numbers may be scrambled since they are assigned before the final ORDER BY is accomplished.

Partitioning (Windowing) Clause

The PARTITION BY clause may be added to the OVER to achieve the objective of grouping the rankings by another criteria.  In this case the ranking starts over for each distinct value of DEPTNO.

SELECT DEPTNO, LASTNAME, SALARY,            
       RANK() OVER(PARTITION BY DEPTNO        
                               ORDER BY SALARY DESC) AS R 
FROM EMP                                      
ORDER BY DEPTNO, SALARY DESC

3.jpg

Check out the full documentation on these functions here:
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_olapspecification.html?lang=en 

David Simpson
Themis Education
dsimpson@themisinc.com

2 Likes
Recent Stories
Some of my favorite tunings tips and tricks for DB2 by Joe Geller

SQL Tuning for Developers – Part 1

The Key To Mastering Db2 Locking On Row-Store Tables