The SQL LIMIT keyword allows us to specify the number of records in the result set.
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 with LIMIT to specify the starting rows from where to select the data. For example,
-- LIMIT 2 selects two results
-- OFFSET 3 excludes the first three results
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.
Note: The LIMIT 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, PostgreSQL (as part of SQL:2008 standard), DB2 |
SQL TOP Clause
The TOP keyword is used in place of LIMIT with the following database systems:
- SQL Server
- MS Access
Let's look at 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.
We can also use * with TOP to select all columns.
SELECT TOP 2 *
FROM Customers;
Here, the SQL command selects the first 2 rows from the table.
SQL FETCH FIRST Clause
The FETCH FIRST n ROWS ONLY clause is used with the Oracle database system.
Let's look at an example.
SELECT *
FROM Customers
FETCH FIRST 2 ROWS ONLY;
Here, the SQL command selects the first 2 rows from the table.
More Ways To Work With SELECT TOP
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 clause.
Also Read: