SQL SELECT LIMIT, TOP, FETCH FIRST

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.

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

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

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.

How to use SQL TOP Clause
Example: SQL TOP Clause

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

PERCENT in 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 in 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 clause.


Also Read:

Did you find this article helpful?