SQL Select Top
The SQL SELECT TOP Clause
The number of rows to be returned is specified using the SELECT TOP clause.
With huge tables that contain thousands of records, the SELECT TOP clause comes in handy. Performance may suffer if a lot of records are returned.
Example
Select only the first 3 records of the Customers table:
SELECT TOP 3 * FROM Customers;
Note: The SELECT TOP clause is not supported by every database system. While Oracle employs FETCH FIRST n ROWS ONLY and ROWNUM, MySQL includes the LIMIT clause, which allows you to choose a finite amount of records.
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):
------ EXAMPLE MUKAVU-----
Demo 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 |
LIMIT
The corresponding MySQL sample is displayed in the following SQL statement:
Example
Select the first 3 records of the Customers table:
SELECT * FROM Customers
LIMIT 3;
FETCH FIRST
The corresponding Oracle sample is displayed in the SQL statement that follows:
Example
Select the first 3 records of the Customers table:
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;
SQL TOP PERCENT Example
For SQL Server/MS Access, the following SQL statement chooses the first 50% of the records from the “Customers” table:
Example
SELECT TOP 50 PERCENT * FROM Customers;
The corresponding Oracle sample is displayed in the SQL statement that follows:
Example
SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;
ADD a WHERE CLAUSE
For SQL Server/MS Access, the following SQL statement chooses the first three entries from the “Customers” table where the nation is “Germany”:
Example
SELECT TOP 3 * FROM Customers
WHERE Country='Germany';
The following SQL statement shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;
The following SQL statement shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
WHERE Country='Germany'
FETCH FIRST 3 ROWS ONLY;
ADD the ORDER BY Keyword
To sort the result, add the ORDER BY keyword and return the first three records in the sorted result.
Regarding MS Access and SQL Server:
Example
Sort the result reverse alphabetically by CustomerName, and return the first 3 records:
SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;
The following SQL statement shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
The following SQL statement shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;
