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.

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net