SQL: MySQL analytic function emulations

gistfile1

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)



SQL> select deptno, ename, sal, sum(sal) over (partition by deptno)
  2  from emp
  3  order by 1, 3
  4  /

    DEPTNO ENAME         SAL     SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- ---------- -------------------------------
        10 MILLER           1300                            8750
        10 CLARK            2450                            8750
        10 KING             5000                            8750
        20 SMITH             800                           10875
        20 ADAMS            1100                           10875
        20 JONES            2975                           10875
        20 SCOTT            3000                           10875
        20 FORD             3000                           10875
        30 JAMES             950                            9400
        30 MARTIN           1250                            9400
        30 WARD             1250                            9400
        30 TURNER           1500                            9400
        30 ALLEN            1600                            9400
        30 BLAKE            2850                            9400

14 rows selected.

mysql> select a.DEPTNO, a.ENAME, a.SAL, b.TOTSAL
    -> from EMP as a
    ->       inner join (select DEPTNO, sum(SAL) TOTSAL
    ->                   from EMP
    ->                   group by DEPTNO) as b
    ->               on a.DEPTNO = b.DEPTNO
    -> order by 1, 3;
+--------+--------+------+--------+
| DEPTNO | ENAME  | SAL  | TOTSAL |
+--------+--------+------+--------+
|     10 | MILLER | 1300 |   8750 |
|     10 | CLARK  | 2450 |   8750 |
|     10 | KING   | 5000 |   8750 |
|     20 | SMITH  |  800 |  10875 |
|     20 | ADAMS  | 1100 |  10875 |
|     20 | JONES  | 2975 |  10875 |
|     20 | SCOTT  | 3000 |  10875 |
|     20 | FORD   | 3000 |  10875 |
|     30 | JAMES  |  950 |   9400 |
|     30 | WARD   | 1250 |   9400 |
|     30 | MARTIN | 1250 |   9400 |
|     30 | TURNER | 1500 |   9400 |
|     30 | ALLEN  | 1600 |   9400 |
|     30 | BLAKE  | 2850 |   9400 |
+--------+--------+------+--------+
14 rows in set (0.00 sec)



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.

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)

These statements seem to be for Oracle, not MySQL. Not sure what you mean by emulations.