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.

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.