SQL Select Top Clause



SQL SELECT TOP Clause :

Suppose we want to fetch only some limited records from the record set then we can use these top clause. It is not supported by all the databases.

We are having different alternate syntaxes for different DB’s

Database Vendor Clause Supported
SQL Server TOP Clause
MS Access Syntax TOP Clause
MySQL LIMIT
Oracle ROWNUM

Demo Database :

Consider the following demo table –

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Omkar    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

A. Top Clause

Syntax of the clause is as below –

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

Consider below query on demo database –

SQL> SELECT TOP 3 * FROM Employee;

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
+----+----------+-----+-----------+----------+

B. LIMIT in MySQL

Syntax for using LIMIT is as below –

SQL> SELECT column_name(s)
FROM table_name
LIMIT number;

Consider below query on demo database –

SQL> SELECT * FROM Employee
LIMIT 3;

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
+----+----------+-----+-----------+----------+

C. ROWNUM in Oracle

Syntax for using ROWNUM is as below –

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Consider below query on demo database –

SQL> SELECT * FROM Employee
WHERE ROWNUM <= 3;

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Anand    |  21 | Karachi   |  2000.00 |
+----+----------+-----+-----------+----------+