SQL - TOP, LIMIT or ROWNUM Clause

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

Note: All the databases do not support TOP clause. For example MySQL supports LIMITclause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.

Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:

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

Example:

Consider the CUSTOMERS table having the following records:

  +----+----------+-----+-----------+----------+  | ID | NAME     | AGE | ADDRESS   | SALARY   |  +----+----------+-----+-----------+----------+  |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |  |  2 | Khilan   |  25 | Delhi     |  1500.00 |  |  3 | kaushik  |  23 | Kota      |  2000.00 |  |  4 | Chaitali |  25 | Mumbai    |  6500.00 |  |  5 | Hardik   |  27 | Bhopal    |  8500.00 |  |  6 | Komal    |  22 | MP        |  4500.00 |  |  7 | Muffy    |  24 | Indore    | 10000.00 |  +----+----------+-----+-----------+----------+  

Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

  SQL> SELECT TOP 3 * FROM CUSTOMERS;  

This would produce the following result:

  +----+---------+-----+-----------+---------+  | ID | NAME    | AGE | ADDRESS   | SALARY  |  +----+---------+-----+-----------+---------+  |  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |  |  2 | Khilan  |  25 | Delhi     | 1500.00 |  |  3 | kaushik |  23 | Kota      | 2000.00 |  +----+---------+-----+-----------+---------+  

If you are using MySQL server, then here is an equivalent example:

  SQL> SELECT * FROM CUSTOMERS  LIMIT 3;  

This would produce the following result:

  +----+---------+-----+-----------+---------+  | ID | NAME    | AGE | ADDRESS   | SALARY  |  +----+---------+-----+-----------+---------+  |  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |  |  2 | Khilan  |  25 | Delhi     | 1500.00 |  |  3 | kaushik |  23 | Kota      | 2000.00 |  +----+---------+-----+-----------+---------+  

If you are using Oracle server, then here is an equivalent example:

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

This would produce the following result:

  +----+---------+-----+-----------+---------+  | ID | NAME    | AGE | ADDRESS   | SALARY  |  +----+---------+-----+-----------+---------+  |  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |  |  2 | Khilan  |  25 | Delhi     | 1500.00 |  |  3 | kaushik |  23 | Kota      | 2000.00 |  +----+---------+-----+-----------+--------