SQL TOP, LIMIT or ROWNUM Clause

SQL TOP, LIMIT or ROWNUM Clause : SQL TOP, LIMIT or ROWNUM Clause are explained with examples.

The SQL SELECT TOP Clause

• The SELECT TOP clause is used to specify the number of records to return.
• The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note:

Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access Syntax:

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

MySQL Syntax:

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

Oracle Syntax:

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

 

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

 

SQL TOP, LIMIT and ROWNUM Examples

The following SQL statement selects the first three records from the “Customers” table:

Example

SELECT TOP 3 * FROM Customers;

Output

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

The following SQL statement shows the equivalent example using the LIMIT clause:

Example

SELECT * FROM Customers
LIMIT 3;

Output

The following SQL statement shows the equivalent example using ROWNUM:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

SQL TOP PERCENT Example

The following SQL statement selects the first 50% of the records from the “Customers” table:

Example

SELECT TOP 50 PERCENT * FROM Customers;

 

Output

Number of Records: 2

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	0502

 

ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the “Customers” table, where the country is “Germany”:

Example

SELECT TOP 3 * FROM Customers
WHERE Country=’Germany’;

Output

Number of Records: 3

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
17	Drachenblut Delikatessend	Sven Ottlieb	Walserweg 21	A

 

The following SQL statement shows the equivalent example using the LIMIT clause:

Example

SELECT * FROM Customers
WHERE Country=’Germany’
LIMIT 3;

Output

Number of Records: 3

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
17	Drachenblut Delikatessend	Sven Ottlieb	Walser

 

The following SQL statement shows the equivalent example using ROWNUM:

Example

SELECT * FROM Customers
WHERE Country=’Germany’ AND ROWNUM <= 3;