SQL TOP, LIMIT or ROWNUM Clause

    To display or fetch the top N rows from the table, we would use the SQL TOP or LIMIT or ROWNUM clause. There are various RDBMS which uses the SQL for creating and modifying Relational Database, and all the RDBMS does not use give support for TOP command, for example, MySQL support LIMIT and Oracle support ROWNUM to fetch the top N records, but overall all these commands are used to perform the same operations.

    TOP syntax

    If you are using SQL Server / MS Access To use the TOP command follow this syntax: 

    SELECT TOP number|percent column_name(s)
FROM table_name;

    With Condition 

    SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

    LIMIT syntax

    if you are using MySQL 

    SELECT column_name,.....
FROM table_name
LIMIT number;

    ROWNUM Syntax

    if you are using Oracle 

    SELECT column_name,.....
FROM table_name
WHERE ROWNUM <= number;

    Example For the queries consider this table of students: 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

    Query( SQL Server / MS Access ): Display the top 3 rows from the table students 

    SELECT TOP 3 * FROM students;
    Output 
    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
+------+--------+------+--------+-------+

    Query( MySQL ): Display the top 4 rows from the table students 

    SELECT *
FROM students
LIMIT 4;

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
+------+--------+------+--------+-------+

    Query (Oracle): Display the top 4 rows from the table students: 

    SELECT * FROM students
WHERE ROWNUM <= 3;

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
+------+--------+------+--------+-------+

    Summary

    • To fetch the top N rows from a table we can SQL TOP, or LIMIT or ROWNUM clause.
    • Whether to TOP, LIMIT or ROWNUM depends on which RDBMS you are using.
    • MS Access usesTOP.
    • MySQL uses LIMIT.
    • Oracle uses ROWNUM.
    • We can also use the WHERE clause if we want to fetch the top rows from a specific data set.

    People are also reading:

    Previous Tutorial Next Tutorial