Emulating Analytic (AKA Ranking) Functions with MySQL
Pages: 1, 2, 3, 4
We get a result set that we just need to "push" inside a subquery in the from clause. By applying to it a filter referring to the newly computed column RANK we get what we want:
SQL> select *
2 from (select deptno, empno, ename, sal,
3 rank() over (partition by deptno
4 order by sal desc) "RANK"
5 from emp)
6 where "RANK" <= 2
7 order by deptno, "RANK"
8 /
DEPTNO EMPNO ENAME SAL RANK
---------- ---------- ---------- ---------- ----------
10 7839 KING 5000 1
10 7782 CLARK 2450 2
20 7788 SCOTT 3000 1
20 7902 FORD 3000 1
30 7698 BLAKE 2850 1
30 7499 ALLEN 1600 2
6 rows selected.
SQL>
Note that there is a tie in department 20.
How can we compute a rank with MySQL? A quick Web search will provide you with an answer: by using a subquery in the select list. Saying that Blake has the highest salary in department 30 only means that no one from this department earns more. Because Allen has the second highest salary, only one person in the same department, our friend Blake, gets a bigger bank transfer each month. Computing how many people from the same department earn more than the current employee and adding one to the result yields the desired answer. In the very same fashion as we have done it with Oracle, we can compute the rank for every row, push the query into the from clause and filter on the rank in MySQL. The only difference is that with MySQL, associating an alias to a subquery in the from clause is mandatory.
mysql> select *
-> from (select a.DEPTNO, a.EMPNO, a.ENAME, a.SAL,
-> (select 1 + count(*)
-> from EMP b
-> where b.DEPTNO = a.DEPTNO
-> and b.SAL > a.SAL) RANK
-> from EMP as a) as x
-> where x.RANK <= 2
-> order by x.DEPTNO, x.RANK;
+--------+-------+-------+------+------+
| DEPTNO | EMPNO | ENAME | SAL | RANK |
+--------+-------+-------+------+------+
| 10 | 7839 | KING | 5000 | 1 |
| 10 | 7782 | CLARK | 2450 | 2 |
| 20 | 7902 | FORD | 3000 | 1 |
| 20 | 7788 | SCOTT | 3000 | 1 |
| 30 | 7698 | BLAKE | 2850 | 1 |
| 30 | 7499 | ALLEN | 1600 | 2 |
+--------+-------+-------+------+------+
6 rows in set (0.01 sec)
mysql>
A lovely result. Are we happy? Well, if there is one thing that too many years spent with databases has taught me, it is that tables have a tendency to grow over time. That the nice little trick that gives exactly the result we want had better be tested against a table a tad bigger than 14 lines. I have therefore created, both in Oracle and MySQL, an EMPLOYEES table vaguely similar in structure to EMP, and I have dutifully populated it with a mere 10,000 rows. The is by no means an impressive number of rows, even if companies with 10,000 employees are big corporations. The employees are uniformly (but randomly) spread among six departments. I have asked Oracle to time its queries, and Oracle (10.2) and MySQL (5.0) are running on the very same server. Let's ask for the five biggest salaries for each department.
Here is what we get with Oracle:
SQL> select *
2 from (select deptno, empno, lastname, firstname, sal,
3 rank() over (partition by deptno
4 order by sal desc) "RANK"
5 from employees)
6 where "RANK" <= 5
7 order by deptno, "RANK"
8 /
DEPTNO EMPNO LASTNAME FIRSTNAME SAL RANK
---------- ---------- -------------------- -------------------- ---------- ----------
10 4751 WOLFORD KATHY 7997.34 1
10 10517 HARRIS GARLAND 7993.74 2
10 8135 GRAHAM LIANA 7988.67 3
10 7028 PRATT GLORIA 7980.45 4
10 6694 GEARY SANDRA 7973.03 5
20 7158 WHALEY PATRICIA 7997.44 1
20 10008 JUNKER TOM 7992.63 2
20 1782 SCOTT JOHN 7991.65 3
20 4158 OLIVER BLANCHE 7989.13 4
20 3537 BUCHANAN LESLEY 7982.61 5
30 7892 VALENCIA EDWARD 7993.51 1
30 5491 PEREZ MARTIN 7990.48 2
30 5751 LARSON LEE 7990.4 3
30 5743 PACHECO YOLANDA 7989.91 4
30 7115 COVEY BRANDON 7984.36 5
40 5493 KOFFLER DENNIS 7998.95 1
40 4123 MURRAH MARY 7997.23 2
40 8103 MORGAN ASHLEY 7995.9 3
40 6171 WINSTEAD DOROTHY 7993.35 4
40 7098 FERRELL HECTOR 7987.78 5
50 9860 ORMAN GUADALUPE 7993.79 1
50 7446 KURLAND GARY 7989.36 2
50 8470 THOMAS JAMES 7989.11 3
50 9251 SOLANO JULIE 7988.24 4
50 2769 MILTON DEBORAH 7986.29 5
60 2899 FARMER FRANK 7981.1 1
60 5021 COCHRAN GARY 7978.5 2
60 6607 MOORE DONALD 7977.7 3
60 4013 BURMEISTER HATTIE 7973.04 4
60 1472 LADD JEFFREY 7952.5 5
30 rows selected.
Elapsed: 00:00:00.05
SQL>
