SQL LIMIT, ROWNUM, TOP Clause

The SQL LIMIT clause is used to indicate the number of records to return.

Note: The LIMIT clause is not supported in all SQL database systems. MySQL and MariaDB support the LIMIT clause, whereas Oracle provides the ROWNUM clause as equivalent to the LIMIT clause, and SQL SERVER and MS Access provides the TOP clause for the SELECT statement to limit the number of records returned by a query.

Syntax

MySQL/MariaDB LIMIT syntax

The syntax for the LIMIT clause is MySQL/MariaDB is as follows:

SELECT column_list
FROM table_name
LIMIT number;

Parameters:

• column_list: The columns that you want to fetch
• table_name: The table that you want to fetch records from
• number: It specifies a limited number of rows returned by the result set.

SQL Server/ MS Access TOP syntax

The syntax for the TOP clause in SQL Server/ MS Access is as follows:

SELECT TOP number|percent column_list
FROM table_name;

Oracle ROWNUM syntax

The syntax for the ROWNUM clause in Oracle is as follows:

SELECT column_list
FROM table_name
WHERE ROWNUM <= number;

Demo Table

Let us suppose that we have a table called "employee" with the following records:

SQL LIMIT, ROWNUM, TOP Examples

The following SQL statement will return the first four rows from the "Employee" table.

• LIMIT clause for MySQL/MariaDB

  SELECT * 
  FROM employee
  ORDER BY emp_no
  LIMIT 2;
  

• ROWNUM clause for Oracle

  SELECT *
  FROM employee
  ORDER BY emp_no
  WHERE ROWNUM <= 2;
  

• TOP clause for SQL Server/MS Access

  SELECT TOP(2) * 
  FROM employee
  ORDER BY emp_no;
  

After the execution of one of the above SQL statement depending on the database system that you are using, the output will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Note: In a SELECT statement, always use an ORDER BY clause with the LIMIT, ROWNUM, TOP clause. So that you be sure to get the desired result.

Setting Row Offset in LIMIT Clause

The LIMIT clause allows an optional second parameter.

When two parameters are passed to the LIMIT clause, the first parameter indicates the offset of the starting point, whereas the second parameter indicates the maximum number of records to return.

The offset of the initial record is 0 (not 1).

The following SQL statement will output the second-highest-paid employee.

SELECT *
FROM employee
ORDER BY salary DESC
LIMIT 1, 1;

After running the above SQL statement, the output will be as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
+--------+-------------+-----------+------------+------------+-----------+------------------+

SQL TOP PERCENT

The following SQL statement will return the first 50% of the rows from the "Employee" table.

The example below is for SQL Server/ MS Access:

SELECT TOP 50 PERCENT *
FROM employee
ORDER BY emp_no;

After the execution, the output will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+