SQL TOP, LIMIT or ROWNUM Clause

By | September 8, 2020
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.

Vamware

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.

Leave a Reply

Your email address will not be published. Required fields are marked *