SQL SELECT TOP | LIMIT | ROWNUM Clause
The SQL SELECT TOP | LIMIT | ROWNUM clause is used to retrieves TOP N number or X percent records from query result.
The SQL SELECT TOP clause is used to fetch or extract specify the number of records returned based on a fixed value or percentage.
The SQL SELECT TOP clause is mainly used to make performance while handling large number of records from query result.
Note: All the databases do not support TOP clause.
For Example:
- SQL "TOP" clause will work only on Microsoft Sql Server and Microsoft Access
- SQL "LIMIT" clause will work only on MySql
- SQL "ROWNUM" clause will work only on Oracle
Related Links
SQL SELECT TOP | LIMIT | ROWNUM Syntax
The below syntax is used to for Microsoft Sql Server and Microsoft Access.
SELECT TOP number|percent column_name1, column_name2
FROM table_name;
The below syntax is used to for MySql.
SELECT column_name1, column_name2 FROM table_name
LIMIT number;
The below syntax is used to for Oracle.
SELECT column_name1, column_name2 FROM table_name
WHERE ROWNUM <= number;
Note:
- TOP (value) - It will return the top number of rows or records in the result set.
- PERCENT - Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set.
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | PHP And MySQL Bible | 136.33 | 2010 | Optimization | Chandra |
2 | Teach Yourself SQL | 165 | 2013 | Performance | Devi Mai |
3 | Red Gate Guide to SQL Server | 105 | 2007 | Administration | Vinoth Kumar |
4 | Head First SQL Brain On SQL | 190 | 2009 | Programming | Sakunthala |
5 | Natural language Query To SQL | 155 | 2007 | Security | Pandurengan |
6 | Oracle Database 11G New Features | 123.45 | 2008 | Performance | Chandra |
SQL SELECT TOP Example
The following SQL statement selects the first 2 records from the "Books" table:
For Microsoft SQL Server / MS Access
SELECT Top 2 * FROM Books;
For MySql
SELECT * FROM Books LIMIT 2;
Note: You can use column names insteadof "*". Column names are separated by commas(,).
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | PHP And MySQL Bible | 136.33 | 2010 | Optimization | Chandra |
2 | Teach Yourself SQL | 165 | 2013 | Performance | Devi Mai |
The following SQL statement selects the last 2 records from the "Books" table:
For Microsoft SQL Server / MS Access
SELECT Top 2 * FROM Books
Order By BookID DESC;
For MySql
SELECT * FROM Books
ORDER BY BookID DESC LIMIT 2
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
6 | Oracle Database 11G New Features | 123.45 | 2008 | Performance | Chandra |
5 | Natural language Query To SQL | 155 | 2007 | Security | Pandurengan |
SQL SELECT TOP PERCENT Example
The following SQL statement selects the column "DomainName" has value "Performance" from the "Books" table and then it extracts first 50% of the records from the result:
For Microsoft SQL Server / MS Access
SELECT Top 50 PERCENT * FROM Books
WHERE DomainName = 'Performance';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | Teach Yourself SQL | 165 | 2013 | Performance | Devi Mai |
Related Links