SQL LIMIT, TOP and FETCH FIRST

In this tutorial, we'll learn about the SQL LIMIT, TOP and FETCH FIRST constraints with the help of examples.

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.

How to use SQL TOP Clause
Example: SQL TOP Clause

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.

How to use SQL LIMIT Clause with OFFSET
Example: SQL LIMIT Clause with OFFSET

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

PERCENT Clause With 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).

WITH TIES Clause With TOP

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.

Did you find this article helpful?