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:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
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
❮ SQL Keywords