SQLite

SQL Limit

The limit and offset clauses in SQL allow you to limit the number of records that are returned by a select statement.

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:

SELECT
    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:

CREATE TABLE employees (
    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:

SELECT * FROM employees;

This should return all the rows as shown:

Suppose we want the first 3 records. For that, we can use the LIMIT clause as:

SELECT * FROM employees LIMIT 3;

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:

SELECT * FROM employees LIMIT 3 offset 3;

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list