OFFSET

So, we know how to get the 5 most expensive cars in the cars table.

SELECT *
FROM cars
ORDER BY asking_price_usd DESC, miles_driven
LIMIT 5;

How would be get the next 5 after that (the 5th through 10th most expensive). We could change it to LIMIT 10 and just look at the bottom 5. That would work, but sometimes we need to fetch rows in increments.

You've probably seen websites that show results in pages. When you finish 1 page, you click a button to go to the next page where more results are loaded.

Suppose each page has 5 cars. If you wanted to implement something like that, you could start with LIMIT 5, wait for the user to go to the next page, then repeat the query but with LIMIT 10, then LIMIT 15 etc. However, this is wasteful. Your database and your server are doing duplicate re-fetching previous pages. Instead, you can use OFFSET.

SELECT *
FROM cars
ORDER BY asking_price_usd DESC, miles_driven
LIMIT 5
OFFSET 5;

While LIMIT defines the number of rows in the result, OFFSET says how many should be skipped from the top. Because we have ORDER BY asking_price_usd DESC, the effect off OFFSET 5 is to skip 1st 5 most expensive cars. The results will contain the 6th through 10th most expensive.

When we need the 3rd page…

SELECT *
FROM cars
ORDER BY asking_price_usd DESC, miles_driven
LIMIT 5
OFFSET 10;