SQLite for Beginners: LIMIT, OFFSET & Pagination Explained | Episode 8

SQLite for Beginners: LIMIT, OFFSET & Pagination Explained

When querying databases, you often don't want to retrieve every row at once. SQLite's LIMIT clause lets you specify how many rows to return, while OFFSET allows you to skip a number of rows, enabling efficient pagination. Combining these with ORDER BY helps you fetch top-N results or browse data page by page.

Code

-- Create a table of countries with population and area
CREATE TABLE countries (
  id INTEGER PRIMARY KEY,
  name TEXT,
  population INTEGER,
  area INTEGER
);

-- Insert 10 most populated countries (sample data)
INSERT INTO countries (name, population, area) VALUES
  ('China', 1409517397, 9596961),
  ('India', 1339180127, 3287263),
  ('United States', 324459463, 9833517),
  ('Indonesia', 263991379, 1904569),
  ('Brazil', 209288278, 8515767),
  ('Pakistan', 197015955, 881912),
  ('Nigeria', 190886311, 923768),
  ('Bangladesh', 164669751, 147570),
  ('Russia', 143989754, 17098242),
  ('Mexico', 129163276, 1964375);

-- Select all countries
SELECT * FROM countries;

-- Get top 3 most populated countries
SELECT name, population
FROM countries
ORDER BY population DESC
LIMIT 3;

-- Get top 5 most populated countries
SELECT name, population
FROM countries
ORDER BY population DESC
LIMIT 5;

-- Pagination example: page 1 (first 5 countries by population)
SELECT name, population
FROM countries
ORDER BY population DESC
LIMIT 5 OFFSET 0;

-- Pagination example: page 2 (next 5 countries by population)
SELECT name, population
FROM countries
ORDER BY population DESC
LIMIT 5 OFFSET 5;

-- Skip top 3 largest countries by area and show the rest
SELECT name, area
FROM countries
ORDER BY area DESC
LIMIT 10 OFFSET 3;

Key Points

• LIMIT restricts the number of rows returned, such as LIMIT 3 to get only three rows.
• OFFSET skips a specified number of rows, useful for pagination like LIMIT 5 OFFSET 5 for page 2.
• ORDER BY combined with LIMIT helps retrieve top-N results, such as the top 3 most populated countries.
• Pagination is achieved by using both LIMIT and OFFSET to fetch data page by page.
• Using these clauses together enables efficient and controlled data retrieval from large datasets.