SQL Select Top

The SQL SELECT TOP clause is used to select the limited number of records from the table in the database.
The SELECT TOP is very suitable for big tables to select limited records.
The Top clause shows the specific number of rows from the table.
All databases do not support this TOP keyword.
MySQL uses the LIMIT keyword, whereas Oracle uses the ROWNUM keyword for selecting the number of records from the database's table.

Syntax

SELECT TOP in SQL server/MS Access.


SELECT TOP number|percent column_name1,column_name2...column_nameN
FROM Table_name
WHERE [condition];

LIMIT clause in MySQL.


SELECT column_name1,column_name2... column_nameN
FROM Table_name
WHERE [condition]
LIMIT value;

ROWNUM keyword in old Oracle.


SELECT column_name1, column_name2....column_nameN
FROM Table_name
WHERE ROWNUM <= number;

Syntax for Oracle 12.


SELECT column_name1, column_name2...column_nameN
FROM Table_name
WHERE [condition]
FETCH FIRST number|percent  ROWS ONLY;

Examples

Following is the demo table for discussion


+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerale    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+

Example with TOP clause


mysql> SELECT TOP 4 * FROM student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
+--------+---------+------+-----------+
4 rows in set (0.01 sec)

Example with LIMIT clause


mysql> SELECT * FROM Student LIMIT 4;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
+--------+---------+------+-----------+
4 rows in set (0.01 sec)

Example with ROWNUM Keyword


mysql> SELECT * FROM Student WHERE  ROWNUM <=4;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
+--------+---------+------+-----------+
4 rows in set (0.01 sec)

Example with FETCH FIRST


mysql> SELECT * FROM Student FETCH FIRST 4 ROWS ONLY;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
+--------+---------+------+-----------+
4 rows in set (0.01 sec)

<<Previous - Alias

Next - Aggregate Functions>>

SQL SELECT TOP rows from table

Syntax

Examples