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: