Top N Queries in SQL

One query that is one often encounters is to answer the question, given data in a database table, how do we obtain the TOP N based on some column of the table ?
There are a few ways to do this, but I will present the most popular 2 methods.

I will illustrate using the following table, emp:

SELECT * FROM emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

Suppose we wish to obtain the top 5 employees by salary, how can one do this ?

  1. Using the RANK() function.
    In databases which provide the RANK function, we can obtain the top 5 employees
    by salary using the following query:SELECT empno, ename, sal
    FROM (SELECT empno, ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
    FROM emp)
    WHERE sal_rank <= 5;
    +-------+-------+---------+
    | empno | ename | sal |
    +-------+-------+---------+
    | 7839 | KING | 5000.00 |
    | 7788 | SCOTT | 3000.00 |
    | 7902 | FORD | 3000.00 |
    | 7566 | JONES | 2975.00 |
    | 7698 | BLAKE | 2850.00 |
    +-------+-------+---------+
    5 rows returned in 0.02 seconds
  2. Using an expression that limits the number of rows returned from an ordered SQL result set.

The list of expressions for the databases are shown below:

 

Database Expression
Oracle ROWCOUNT
MySQL/PostgreSQL/Vertica LIMIT
Sybase ROWNUM
MS SQL TOP

ii. Here are the corr. queries for each database:

Vertica/PostgreSQL/MySQL
SELECT empno, ename, sal FROM (SELECT empno, ename, sal ORDER BY sal DESC) a
LIMIT 5;

Oracle
SELECT * FROM (SELECT empno, ename, sal FROM emp ORDER BY sal DESC) A WHERE ROWNUM <= 5;
Sybase
SET ROWCOUNT 10;
SELECT empno, ename, sal FROM emp ORDER BY sal DESC;

MS SQL
SELECT TOP 5 FROM (SELECT empno, ename, sal FROM emp ORDER BY sal DESC);