SQL · Theory

Limit

Theory

290/439

Limit

LIMIT 10 returns at most 10 rows. LIMIT 20, 10 (or LIMIT 10 OFFSET 20) skips 20 then takes 10 — page 3 of size 10 if you started at 0. Always ORDER BY when paging or the page is random.

Topper — ORDER BY marks DESC LIMIT 1. Pagination: ORDER BY roll LIMIT 10 OFFSET 20.

Trap — OFFSET 100000 on a huge table (deep pagination is expensive). Trap: LIMIT without ORDER BY in ‘top N’ answers.

On the example next to this theory: Limit: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

ORDER BY marks DESC LIMIT 5; — top 5.

Limit — sample query

-- Limit
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Limit: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefLIMIT n caps rows. OFFSET skips rows.
RuleORDER BY for top-N and pages.
TrapLIMIT without ORDER BY. Huge OFFSET.

SQL · Theory

Limit

← All stacks

Theory

290/439

Limit

LIMIT 10 returns at most 10 rows. LIMIT 20, 10 (or LIMIT 10 OFFSET 20) skips 20 then takes 10 — page 3 of size 10 if you started at 0. Always ORDER BY when paging or the page is random.

Topper — ORDER BY marks DESC LIMIT 1. Pagination: ORDER BY roll LIMIT 10 OFFSET 20.

Trap — OFFSET 100000 on a huge table (deep pagination is expensive). Trap: LIMIT without ORDER BY in ‘top N’ answers.

On the example next to this theory: Limit: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

ORDER BY marks DESC LIMIT 5; — top 5.

Limit — sample query

-- Limit
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Limit: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefLIMIT n caps rows. OFFSET skips rows.
RuleORDER BY for top-N and pages.
TrapLIMIT without ORDER BY. Huge OFFSET.

Limit

Limit — sample query

Short notes

Questions

Limit

Limit — sample query

Short notes

Questions