OFFSET with LIMIT for pagination

OFFSET paired with LIMIT slices a full result set into evenly sized chunks by skipping a set number of rows before returning the next batch. Like tearing a long receipt into equal strips - each strip covers a different section, and together they account for the whole thing.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Skipping rows with OFFSET

SELECT * FROM orders LIMIT 5 OFFSET 5

Result:

order_id	customer_id	amount
6	C006	19.99
7	C007	62.4

Step by step

Start with just LIMIT - you get the first 5 rows.
```
SELECT * FROM orders
LIMIT 5;
```
OFFSET 5 skips the first 5 rows.
```
SELECT * FROM orders
LIMIT 5 OFFSET 5;
```
OFFSET 10 skips 10 rows.
```
SELECT * FROM orders
LIMIT 5 OFFSET 10;
```

Practice

Try each one in your head first, then open the answer.

1. Write a query on the orders table that returns 2 rows, skipping the first 2.

SELECT * FROM orders LIMIT 2 OFFSET 2

2. Write a query on the products table that returns 3 rows, skipping the first 3.

SELECT * FROM products LIMIT 3 OFFSET 3

3. Write a query on the store_sales table that returns 3 rows, skipping the first 6.

SELECT * FROM store_sales LIMIT 3 OFFSET 6

4. Write a query on the reviews table that returns 3 rows, skipping the first 3.

SELECT * FROM reviews LIMIT 3 OFFSET 3

5. Write a query on the employees table that returns 5 rows, skipping the first 10.

SELECT * FROM employees LIMIT 5 OFFSET 10

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed