CODEWITHSUNDEEP
mysql
Arun Killu
Arun Killu

Reputation: 14233

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?

Upvotes: 194

Views: 203027

Answers (5)

w.Daya
w.Daya

Reputation: 530

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.

Upvotes: 1

A Pali
A Pali

Reputation: 1

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.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 258

vineet
vineet

Reputation: 14236

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

Upvotes: 96

Mahesh Patil
Mahesh Patil

Reputation: 1551

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

Upvotes: 4

Related Questions