Limit — SQL Technical Interview Questions & Answers

Limit

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.

Technical Interview Tip: ORDER BY marks DESC LIMIT 5; — top 5.

Code Examples for Limit

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.

How to Explain Limit to an Interviewer

Top 5 students?

ORDER BY marks DESC LIMIT 5