Hack 40. Calculate Rank

The RANK( ) function introduced in ISO SQL:2003 has been implemented in Oracle and SQL Server. RANK( ) allows the efficient calculation of rank position.

Suppose you have the results of the sales figures achieved by your sales force, as shown in Table 5-23.

Table 5-23. The sales table

ID	Name	totValue	totVolume
1	Loman	4000	49
2	Miller	3000	49
3	Hoffman	3000	85
4	Cobb	2000	66
5	Mitchell	4000	96

You can use the ORDER BY clause to see these by value or by volume:

SQL> SELECT * FROM sales ORDER BY totValue DESC;

 ID NAME TOTVALUE TOTVOLUME
---------- -------------------- ---------- ----------
 1 Loman 4000 49
 5 Mitchell 4000 96
 2 Miller 3000 49
 3 Hoffman 3000 85
 4 Cobb 2000 66

With RANK( ) you can return the rank position for any column:

SQL> SELECT name,
 2 totValue,
 3 RANK( ) OVER (ORDER BY totValue DESC) r
 4 FROM sales
 5 ORDER BY totValue DESC;

NAME TOTVALUE R
-------------------- ---------- ----------
Loman 4000 1
Mitchell 4000 1
Miller 3000 3
Hoffman 3000 3
Cobb 2000 5

Better yet, you can show the rank position for more than one column at a time and you can order the result any way you choose:

SQL> SELECT name,
 2 totValue,
 3 RANK( ) OVER (ORDER BY totValue DESC) rVal,
 4 totVolume,
 5 RANK( ) OVER (ORDER BY totVolume DESC) rVol
 6 FROM sales
 7 ORDER BY name;

NAME TOTVALUE RVAL TOTVOLUME RVOL
---------- ---------- ---------- ---------- ----------
Cobb 2000 5 66 3
Hoffman 3000 3 85 2
Loman 4000 1 49 4
Miller 3000 3 49 4
Mitchell 4000 1 96 1

You can see that Mitchell and Loman are tied for top salesman by value, but Mitchell is also the top salesman by volume.

MySQL 5.0 does not support the RANK( ) function.