Sometimes you do not want every single row. Maybe you just want to peek at the data,
or you only need the top 5 results. That is what LIMIT does.
Basic LIMIT
SELECT * FROM students LIMIT 3;This gives you only the first 3 rows. It is great for when you are exploring a table with thousands of rows and just want to see what the data looks like.
LIMIT with ORDER BY
This is where LIMIT really shines — combined with ORDER BY to get top results:
-- Top 3 students by score
SELECT name, score FROM students ORDER BY score DESC LIMIT 3;This gives you the top 3 scorers. Without ORDER BY, LIMIT just gives you whatever rows the database feels like returning first, which is usually not what you want.
OFFSET — Skip Some Rows
LIMIT and OFFSET are best friends. OFFSET lets you skip a certain number of rows before starting to return results. This is how pagination works:
-- Skip the first 2 rows, then give me 3 rows
SELECT * FROM students LIMIT 3 OFFSET 2;
If you are building a website with a "next page" button, you would use something like
LIMIT 10 OFFSET 0 for page 1, LIMIT 10 OFFSET 10 for page 2,
and so on.
SQLite Shortcut
In SQLite (which our compiler uses), you can write LIMIT and OFFSET together:
SELECT * FROM students LIMIT 3 OFFSET 2;
-- Same thing, shorter:
SELECT * FROM students LIMIT 2, 3;
-- Wait — this is confusing. The first number is OFFSET, second is LIMIT.
-- I honestly prefer the LIMIT ... OFFSET ... version because it is clearer.