This article will look at using the limit and offset clauses in SQL.
NOTE: It is good to remember that the SQL LIMIT and OFFSET clauses are not part of the ANSI SQL standard. However, it is widely adopted by major database engines.
Syntax
The syntax for the limit and offset clauses is as shown below:
col1, col2,…colN
FROM
TABLE_NAME
ORDER BY column_name
LIMIT COUNT OFFSET offset;
The limit value will determine the number of rows/records returned from the query.
The offset value allows you to skip specific rows before beginning to fetch for matching records.
NOTE: The OFFSET value is not required. If missing, the query will fetch records from the first row.
Example
Assume you have a sample table and data as shown in the query below:
id serial PRIMARY KEY,
full_name VARCHAR(255),
email VARCHAR(255),
department VARCHAR(100),
start_date DATE,
active bool,
category VARCHAR(50)
);
INSERT INTO employees (full_name, email, department, start_date, active, category)
VALUES
('Meghan Edwards', '[email protected]', 'Game Development', '2016-06-22', TRUE, 'DEV-G1'),
('Sam Davis', '[email protected]', 'Game Development', '2014-01-04', FALSE, 'DEV-G1'),
('Taylor Miller', '[email protected]', 'Game Development', '2019-10-06', TRUE, 'DEV-G1'),
('Wilson Smitty', '[email protected]', 'Database Developer', '2016-12-23', TRUE, 'DEV-DB1'),
('Barbara Moore', '[email protected]', 'Database Developer', '2015-12-23', TRUE, 'DEV-DB1'),
('Raj Chan', '[email protected]', 'Database Developer', '2017-12-23', FALSE, 'DEV-DB1'),
('Susan Brown', '[email protected]', 'DevOps Engineer', '2011-12-23', TRUE, 'DEV-OPS1'),
('Marial Anderson', '[email protected]', 'DevOps Engineer', '2015-12-23', TRUE, 'DEV-OPS1'),
('Nancy Thomas', '[email protected]', 'DevOps Engineer', '2014-12-23', FALSE, 'DEV-OPS1');
You can view all the records in the table using the select query as shown:
This should return all the rows as shown:
Suppose we want the first 3 records. For that, we can use the LIMIT clause as:
The above query should return the first three rows starting from 1. The output is as shown:
What if you want to retrieve the following three elements? We can use the limit and offset clause as illustrated below:
The query above will return three elements starting from the fourth row (inclusive). If you have an id for your rows, the output should be descriptive as shown:
Terminating
As illustrated in this article, the limit and offset clauses are a safety feature that prevents you from fetching thousands, if not millions, of records in a single query. Therefore, it is good to utilize this feature for simple pagination and pretty output.
