LIMIT

Learning Focus

Use this lesson to understand LIMIT with practical syntax and examples.

Concept Overview

Definition

LIMIT restricts how many rows a query returns. It is commonly used for UI pages, previews, debugging, and top-N queries.

In PostgreSQL, LIMIT is most valuable when it is paired with:

• ORDER BY for deterministic results
• OFFSET for page-style navigation (with performance tradeoffs)
• keyset pagination (WHERE ... > last_seen) for scalable paging

Why is it important?

• Performance: returning fewer rows reduces network and client work
• Safety: limits prevent accidental “select the whole table” reads
• User experience: pagination depends on limiting result size

Where does it fit?

LIMIT is typically used with SELECT (DQL). You will also see it in scripts that preview data before UPDATE/DELETE operations.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression
LIMIT count
OFFSET start;

Available Options / Parameters

Option	Meaning	Notes
LIMIT count	return at most count rows	count is an expression; use a small integer literal for clarity
OFFSET start	skip start rows before returning results	large offsets can be slow on big tables
FETCH FIRST n ROWS ONLY	SQL-standard form of LIMIT	equivalent to LIMIT n
LIMIT ALL	no limit	useful when building dynamic SQL

Key Rules and Considerations

• LIMIT is applied after filtering (WHERE) and ordering (ORDER BY).
• Without ORDER BY, the “first N rows” are not deterministic.
• OFFSET pagination can get slower as OFFSET grows (the database still has to walk past skipped rows).
• For stable pagination, use a deterministic ordering and include a tie-breaker (often the primary key).

---

Step-by-Step Examples

Example 1: Basic LIMIT with a Deterministic Order (Beginner)

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      integer NOT NULL CHECK (salary >= 0)
);

INSERT INTO employees (full_name, department, salary)
VALUES
  ('Ana Ng', 'IT', 68000),
  ('Ben Ito', 'Marketing', 72000),
  ('Cora Li', 'IT', 91000),
  ('Drew Park', 'Sales', 64000),
  ('Ema Sol', 'Finance', 86000);

SELECT employee_id, full_name, department
FROM employees
ORDER BY employee_id
LIMIT 3;

Expected output:

 employee_id | full_name  | department
-------------+------------+------------
 1           | Ana Ng     | IT
 2           | Ben Ito    | Marketing
 3           | Cora Li    | IT
(3 rows)

---

Example 2: LIMIT + OFFSET for Page-Style Pagination (Intermediate)

-- Page size = 2
-- Page 2 means: skip the first 2 rows, return the next 2.

SELECT employee_id, full_name, department
FROM employees
ORDER BY employee_id
LIMIT 2 OFFSET 2;

Expected output:

 employee_id | full_name  | department
-------------+------------+------------
 3           | Cora Li    | IT
 4           | Drew Park  | Sales
(2 rows)

---

Example 3: Keyset Pagination (Scalable Paging) (Advanced)

Keyset pagination avoids large OFFSET values by using a “last seen” key.

-- Page 1
SELECT employee_id, full_name
FROM employees
ORDER BY employee_id
LIMIT 2;

-- Suppose the last row on page 1 has employee_id = 2
-- Page 2
SELECT employee_id, full_name
FROM employees
WHERE employee_id > 2
ORDER BY employee_id
LIMIT 2;

Expected output (page 1):

 employee_id | full_name
-------------+----------
 1           | Ana Ng
 2           | Ben Ito
(2 rows)

Expected output (page 2):

 employee_id | full_name
-------------+----------
 3           | Cora Li
 4           | Drew Park
(2 rows)

This pattern becomes especially important when you paginate over millions of rows.

---

Practical Use Cases

1) Paginating API responses

SELECT order_id, status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 40;

2) Top-N queries for dashboards

SELECT product_id, revenue
FROM daily_product_revenue
WHERE day = current_date
ORDER BY revenue DESC
LIMIT 10;

3) Previewing rows before an UPDATE or DELETE

SELECT user_id, email
FROM users
WHERE last_login_at < now() - interval '365 days'
ORDER BY last_login_at
LIMIT 25;

4) Sampling a large table during debugging

SELECT *
FROM ingestion_errors
ORDER BY occurred_at DESC
LIMIT 100;

5) Keyset pagination for infinite scroll

SELECT message_id, user_id, body
FROM messages
WHERE channel_id = 7
  AND message_id < 5000
ORDER BY message_id DESC
LIMIT 50;

---

Common Mistakes & Troubleshooting

1) Using LIMIT without ORDER BY

Wrong SQL:

SELECT * FROM employees LIMIT 10;

Bad outcome:

• “First 10 rows” is not stable across executions.

Fix:

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10;

---

2) Pagination gets slower as OFFSET grows

Wrong approach:

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 50 OFFSET 500000;

Bad outcome:

• The database must scan/skip a large number of rows.

Fix (keyset pagination):

SELECT *
FROM employees
WHERE employee_id > 500000
ORDER BY employee_id
LIMIT 50;

---

3) Thinking LIMIT filters rows (it does not)

Wrong mental model:

• expecting LIMIT to “remove bad rows” like WHERE.

Fix:

-- Use WHERE to filter, LIMIT to cap the result size
SELECT *
FROM employees
WHERE department = 'IT'
ORDER BY employee_id
LIMIT 10;

---

4) Non-deterministic ordering causes duplicate/missing rows in paging

Wrong approach:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

Bad outcome:

• If multiple rows share the same created_at, pages can shift and cause duplicates/missing rows.

Fix:

SELECT *
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 20;

---

Debugging checklist:

1. Add ORDER BY to make the result deterministic.
2. Include a tie-breaker column in ORDER BY (often the primary key).
3. If you are paginating deep, switch from OFFSET to keyset pagination.
4. Check indexes on your WHERE and ORDER BY columns.
5. Use EXPLAIN to confirm the query plan for large tables.

---

Best Practices

• ✅ Always pair LIMIT with ORDER BY. ❌ Avoid relying on “natural” row order.
• ✅ Include a tie-breaker in ORDER BY for paging (for example, created_at, id). ❌ Avoid ordering by non-unique columns alone.
• ✅ Prefer keyset pagination for large tables and deep paging. ❌ Avoid large OFFSET values in hot paths.
• ✅ Put a reasonable maximum LIMIT in APIs to protect the database. ❌ Avoid unbounded queries from user input.
• ✅ Index what you filter and sort by. ❌ Avoid sorting large result sets without appropriate indexes.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO practice_posts (title, created_at)
VALUES
  ('Post A', '2026-01-01 00:00:00+00'),
  ('Post B', '2026-01-02 00:00:00+00'),
  ('Post C', '2026-01-03 00:00:00+00'),
  ('Post D', '2026-01-04 00:00:00+00'),
  ('Post E', '2026-01-05 00:00:00+00');

Exercise 1 (Easy): Return the newest 2 posts

Task: Select the newest 2 posts.

-- Your SQL here

Solution:

SELECT post_id, title, created_at
FROM practice_posts
ORDER BY created_at DESC, post_id DESC
LIMIT 2;

---

Exercise 2 (Medium): Get page 2 with OFFSET

Task: Use a page size of 2 and return page 2 (rows 3-4 in the chosen ordering).

-- Your SQL here

Solution:

SELECT post_id, title, created_at
FROM practice_posts
ORDER BY created_at ASC, post_id ASC
LIMIT 2 OFFSET 2;

---

Exercise 3 (Advanced): Keyset pagination

Task: Return the next 2 posts after post_id = 2 using keyset pagination.

-- Your SQL here

Solution:

SELECT post_id, title, created_at
FROM practice_posts
WHERE post_id > 2
ORDER BY post_id
LIMIT 2;

---

Connection to Other Concepts

Concept	Why it matters
SELECT	LIMIT caps the returned rows
ORDER BY	determines which rows are in the “top N”
WHERE	filters before LIMIT is applied
Indexes	speed up filtered + ordered queries
Window functions	alternative approach for ranked lists and pagination patterns
NULL handling	sort behavior can change with NULLS FIRST/LAST

---

Visual Learning Diagram

flowchart TD
  A[SELECT] --> B[WHERE]
  B --> C[ORDER BY]
  C --> D[LIMIT/OFFSET]
  D --> E[Pagination]
  D --> F[Top-N]
  D --> G[Preview Before UPDATE/DELETE]
  E --> H[Keyset Pagination]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class D highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Paginating without a stable order	duplicates/missing rows across pages	use deterministic ORDER BY with a tie-breaker
Deep paging with large OFFSET	slow response times	switch to keyset pagination
LIMIT used as a substitute for filtering	wrong results	apply WHERE first, then LIMIT
API lets callers request huge limits	DB overload	enforce maximum limits and timeouts
Sorting without supporting indexes	heavy sorts	add indexes that match filter + order

---

Quick Reference

SELECT * FROM t ORDER BY id LIMIT 10;
SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;
SELECT * FROM t WHERE id > 1000 ORDER BY id LIMIT 10; -- keyset style
SELECT * FROM t FETCH FIRST 10 ROWS ONLY;

What's Next

• Previous: DELETE - Review the previous lesson to reinforce context.
• Next: NULL Values - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.