LIMIT - LabsICT

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;

Try it Yourself →

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;

Try it Yourself →

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;

Try it Yourself →

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.

Try it Yourself →