LIMIT / OFFSET Clauses

03/19/2022 Reading Time: 2 Minutes

One of the last considerations when writing a SQL query are the LIMIT and OFFSET clauses. These clauses by far have the least spectacular impact on a result set, but have valuable utility for specific query and application purposes.

Before I jump into explaining the LIMIT and OFFSET clauses, let me provide the data table used for the examples. “…” represents that data is present between the rows following a sequential order, but for the purpose of this lesson are not relevant and hidden for brevity.

id first_name last_name
1 Nanni Saphin
2 Odell McGlaud
3 Bea McTurlough
4 Naomi Simonett
5 Garek Dalloway
6 Fonz Janikowski
7 Sinclare Smeath
8 Eryn Chable
9 Noel Lapidus
10 Walsh Reaman
... ... ...
45 Pam Maharry
46 Sula Ferrant
47 Margareta Scarfe

LIMIT is a clause that will set the upper limit on the row count displayed in a result set. This is processed before the result set appears meaning that it can positively impact the query processing time.

Two common queries that utilize LIMIT are when querying the first or last n (n representing a value provided by the user) rows in a query and when trying to understand what type of data is present in a query without waiting for the engine to process the data returned by the whole query.

Here is an example of a query that returns the first three results based on user id:

SELECT id, first_name, last_name
FROM employee
LIMIT 3;

Result Set:

id first_name last_name
1 Nanni Saphin
2 Odell McGlaud
3 Bea McTurlough

Here is an example of a query that returns the last seven results based on user id. Note that the ordering of the data will yield different answers to data questions if you aren’t aware.

SELECT id, first_name, last_name
FROM employee
ORDER BY id DESC
LIMIT 3;

Result Set:

id first_name last_name
47 Margareta Scarfe
46 Sula Ferrant
45 Pam Maharry

OFFSET is a clause that will set the starting row for the result set to be displayed. Specifying OFFSET 0 will start the result set at the first row, while OFFSET 8 will start the result set at the ninth row. Note that 0 will always represent the first row when used with this clause.

A common query using OFFSET is pagination. We all consume media on the internet and you may have noticed that each website has a pagination section that breaks up the content into individual pages rather than load all of the media on one page.

SELECT id, first_name, last_name
FROM employee
LIMIT 5
OFFSET 5;

Result Set:

id first_name last_name
6 Fonz Janikowski
7 Sinclare Smeath
8 Eryn Chable
9 Noel Lapidus
10 Walsh Reaman

📬 Subscribe to My Newsletter

Get the latest articles and exclusive content straight to your inbox.