OFFSET — SQL Technical Interview Questions & Answers

OFFSET

OFFSET

  • 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: OFFSET: 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 OFFSET

OFFSET — sample query

-- OFFSET
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;

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

How to Explain OFFSET to an Interviewer

Top 5 students?

ORDER BY marks DESC LIMIT 5