The SELECT TOP command is used to select a fixed number of rows from a database. For example,
SELECT TOP 2 *
FROM Customers;
Here, the SQL command selects the first 2 rows from the table.
Note: The TOP clause is not supported in all Database Management Systems (DBMS). Different DBMS use different keywords to select a fixed number of rows.
For example,
| Keyword | Database System |
|---|---|
TOP |
SQL Server, MS Access |
LIMIT |
MySQL, PostgreSQL, SQLite |
FETCH FIRST |
Oracle |
SQL LIMIT Clause
The LIMIT keyword is used with the following database systems:
- MySQL
- PostgreSQL
- SQLite
Let's see an example,
SELECT first_name, age
FROM Customers
LIMIT 2;
Here, the SQL command selects the first 2 rows from the table.
SQL LIMIT With OFFSET Clause
The OFFSET keyword is used to specify starting rows from where to select rows. For example,
SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;
Here, the SQL command selects 2 rows starting from the fourth row. OFFSET 3 means the first 3 rows are excluded.
SQL TOP Clause
The TOP keyword is used with the following database systems:
- SQL Server
- MS Access
Let's see an example.
SELECT TOP 2 first_name, last_name
FROM Customers;
Here, the SQL command selects first_name and last_name of the first 2 rows.
SQL FETCH FIRST Clause
The FETCH FIRST n ROWS ONLY clause is used with the Oracle database system.
Let's see an example.
SELECT *
FROM Customers
FETCH FIRST 2 ROWS ONLY;
Here, the SQL command selects the first 2 rows from the table.
More SELECT TOP Examples
The PERCENT keyword is used to select the first n percent of total rows. For example,
SELECT TOP 40 PERCENT first_name, last_name
FROM Customers;
Suppose, our table contains 5 rows. In this case, the above SQL command selects 40% of the total rows (2 rows).
The WITH TIES clause is used to select more rows if there are similar values to the last row. Let's take an example
SELECT TOP 3 WITH TIES first_name, country
FROM Customers
ORDER BY country DESC;
Here, the SQL command,
- first sorts the rows by country in descending order.
- Then, the first 3 rows are selected.
- Suppose, the country field in the last row is USA. If the rows after them also contain USA in the country field, those rows will also be selected.
By the way, the ORDER BY keyword must be always used with the WITH TIES.