SQL TOP/LIMIT/ROWNUM CLAUSE - The Coding Shala

Home >> Learn SQL >> SQL TOP/LIMIT/ROWNUM Clause

SQL TOP/LIMIT/ROWNUM CLAUSE

The 'SELECT TOP' Clause is used to specify the number of records to return like top n rows or n percents records. All the databases do not support the TOP clause. For example, MySQL supports the LIMIT clause to fetch the limited number of records while Oracle uses the ROWNUM command to fetch a limited number of records.

SQL TOP CLAUSE SYNTAX

The basic syntax of the SQL TOP Clause is as follows - 

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

In MySQL, we use LIMIT. The basic syntax of LIMIT is as follows - 

SELECT column_name(s)  
 FROM table_name  
 WHERE condition  
 LIMIT number;  

In the Oracle database, we use ROWNUM. The basic syntax of SQL ROWNUM is as follows - 

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

SQL TOP, LIMIT, and ROWNUM EXAMPLES 

The following 'emp' table is used in the examples - 

 Id     Name     Age     Address     Salary  
 1     Akshay     22     Pune       40000  
 2     Mohit      21     Delhi      42000  
 3     Akash      21     Delhi      45000  
 4     Nikhil     24     Mumbai     50000  
 5     Smith      24     Pune       50000  
 6     Akshay     22     Pune       50000  
 7     Nikhil     24     Mumbai     43000  

The following query will fetch top 3 rows from emp table in MySQL database - 

SQL >> select * from emp  
        limit 3;  
 
Output >>   
 Id     Name     Age     Address     Salary  
 1     Akshay    22       Pune     40000  
 2     Mohit     21       Delhi     42000  
 3     Akash     21       Delhi     45000  

The same query using ROWNUM - 

SELECT * FROM emp  
 WHERE ROWNUM <= 3; 

The following query will fetch the first 30% data from the emp table - 

SELECT TOP 30 PERCENT * FROM emp;  

Other Posts You May Like

• SQL LIKE Clause
• SQL With Clause
• SQL Union Clause
• SQL Alter Table
• SQL Delete Table

Prev<< SQL Where Clause                                                                  NEXT >>SQL Having Clause

Please leave a comment below if you like this post or found some error, it will help me to improve my content.