AddThis Social Bookmark Button

Print

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>

Pages: 1, 2, 3, 4

Next Pagearrow




-->