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 multiple sums in one query

sql server datediff years

add constraint unique sql server

mysql insert into syntax

sql select distinct multiple columns

sql group by multiple columns

sql constraints with examples

null value in sql

sql select from temp table

drop columns sql server

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

Related Links

sql union example

date between two dates sql

ms sql server datepart

average of a count sql

basic mysql commands

using like in sql

sql server wildcard characters

mysql create table primary key

sql server max

mysql select and insert into another table

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.