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
You can also search these topics using sql top clause, select top sql, top sql, oracle select top, sql top percent, sql top n, select top 5 sql, mssql select top, top sql server, sql top keyword, top sql command, sql top example, sql top last, sql top bottom, sql server limit.

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
You can also search these topics using sql server limit 1, sql server query limit, sql query limit rows, sql server rownum, sql select first record, sql select first 10 rows, sql select last 10, sql number of rows, sql count rows in table.