SQL Select Top

The SQL SELECT TOP Clause

The SELECT TOP clause specifies the quantity of records to be retrieved.

The SELECT TOP clause is beneficial for optimizing performance on large tables with thousands of records, as retrieving a large number of records can affect performance.

Example

Retrieve solely the initial 3 records from the Customers table.

SELECT TOP 3 * FROM Customers;

 

Note: Not all database systems support the SELECT TOP clause. MySQL employs the LIMIT clause to select a specific number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM to achieve the same result.

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 12 Syntax:

SELECT column_name(s) FROM table_name ORDER BY column_name(s) FETCH FIRST number ROWS ONLY;

Older Oracle Syntax:

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

Older Oracle Syntax (with ORDER BY):

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

Demo Database

Here is an excerpt from the Customers table referenced in the examples:

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

LIMIT

The following SQL statement demonstrates the equivalent example in MySQL:

Example

SELECT * FROM Customers LIMIT 3;

FETCH FIRST

The following SQL statement illustrates the equivalent example in Oracle:

Example

Retrieve the initial 3 records from the Customers table.

SELECT * FROM Customers FETCH FIRST 3 ROWS ONLY;

SQL TOP PERCENT Example

The provided SQL statement selects the top 50% of records from the “Customers” table (for SQL Server/MS Access).

Example

SELECT TOP 50 PERCENT * FROM Customers;

The following SQL statement demonstrates the equivalent example in Oracle:

Example

SELECT * FROM Customers FETCH FIRST 50 PERCENT ROWS ONLY;

ADD a WHERE CLAUSE

The SQL statement below selects the initial three records from the “Customers” table, specifically those where the country is “Germany” (for SQL Server/MS Access):

Example

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

The following SQL statement illustrates the equivalent example in MySQL:

Example

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

The following SQL statement illustrates the equivalent example for Oracle:

Example

SELECT * FROM Customers WHERE Country=‘Germany’ FETCH FIRST 3 ROWS ONLY;

ADD the ORDER BY Keyword

In SQL Server and MS Access, incorporate the “ORDER BY” clause to arrange the results and retrieve the top 3 records from the sorted outcome.

Example

Arrange the result in reverse alphabetical order based on CustomerName, and retrieve the top 3 records.

SELECT TOP 3 * FROM Customers ORDER BY CustomerName DESC;

The subsequent SQL statement provides an equivalent example tailored for MySQL:

Example

SELECT * FROM Customers ORDER BY CustomerName DESC LIMIT 3;