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 VendorClause Supported
SQL ServerTOP Clause
MS Access SyntaxTOP Clause
MySQLLIMIT
OracleROWNUM

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 |
+----+----------+-----+-----------+----------+