SQL ROWNUM Keyword

The SQL ROWNUM keyword is used to fetch specified number of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance. This keyword is supported by Oracle database.

Note: ROWNUM keyword is not supported in all database. For example SQL Server and MS Access supports TOP keyword and MySQL supports the LIMIT keyword to fetch limited number of records.

Syntax

The syntax for using LIMIT keyword is given below:

SELECT column1, column2, ...
FROM table_name
WHERE condition(s) AND ROWNUM <= number;

Example:

Consider a database containing a table called Employee with the following records:

• To fetch top 3 records from the Employee table, the SQL query is:

  SELECT * FROM Employee WHERE ROWNUM <= 3;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800