Which rows are returned when using LIMIT with OFFSET in MySQL?

In the query below:

SELECT column FROM table LIMIT 18 OFFSET 8 

how many results will we get as output and from where to where?

5 Answers

It will return 18 results starting on record #9 and finishing on record #26.

Start by reading the query from offset. First you offset by 8, which means you skip the first 8 results of the query. Then you limit by 18. Which means you consider records 9, 10, 11, 12, 13, 14, 15, 16....24, 25, 26 which are a total of 18 records.

Check this out.

And also the official documentation.

OFFSET is nothing but a keyword to indicate starting cursor in table

SELECT column FROM table LIMIT 18 OFFSET 8 -- fetch 18 records, begin with record 9 (OFFSET 8) 

you would get the same result form

SELECT column FROM table LIMIT 8, 18 

visual representation (R is one record in the table in some order)

 OFFSET LIMIT rest of the table __||__ _______||_______ __||__ / \ / \ / RRRRRRRR RRRRRRRRRRRRRRRRRR RRRR... \________________/ || your result 

You will get output from column value 9 to 26 as you have mentioned OFFSET as 8

Offset is majorly used to support pagination in MySql SELECT statements. First the query will execute and then the records after the offset will be returned.

For example: let's say you want to show 10 reviews per page for a product as per the order of ratings (highest first), then below query can be used to get the reviews which will be shown on third page:

Select * from Reviews where productid= order by ratings desc LIMIT 10 OFFSET 20.

It will skip first 8 records and desplays records from 9 to 26

limit 18 : Display/select 18 records

offset 8 : will skip 8 records

if Your table has ids like 1,2,3,4,5,6,7,8,9,10,11.... and so on , so 1 to 8 records will be skipped and records after 9 to 26 ie 18 records will be displayed/selected.