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

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
7 Oracle Interview Questions 71.87 2007 Optimization Bala Murugan
8 Sql Server T-sql By Example 90 2011 Programming Bala Murugan
9 SQL Server Fundamentals 205 2007 Optimization Vinoth Kumar
10 Foundations Of Sql Server 2008 65 2013 Database Harish Karthik
11 SQL Server 2012 Black Book 178.69 2006 Security Keshavan
12 The Complete Guide to MySql 130 2009 Optimization Pandurengan

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
12 The Complete Guide to MySql 130 2009 Optimization Pandurengan
11 SQL Server 2012 Black Book 178.69 2006 Security Keshavan

SQL SELECT TOP PERCENT Example

The following SQL statement selects the column "DomainName" has value "Optimization" 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 = 'Optimization';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 PHP And MySQL Bible 136.33 2010 Optimization Chandra
7 Oracle Interview Questions 71.87 2007 Optimization Bala Murugan

Search Keys

  • sql server limit 1
  • limit sql server
  • sql server query limit
  • sql query limit rows
  • sql server rownum
  • rownum in sql
  • sql percentage
  • percent sql
  • sql first
  • sql select first record
  • sql select first 10 rows
  • sql first row
  • sql select last 10
  • sql number of rows
  • sql count rows in table