184

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?

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
Arun Killu
  • 13,581
  • 5
  • 34
  • 61

5 Answers5

244

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.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • u are correct ,is there any difference b/w limit 8,18 and limit 18 offset 8 – Arun Killu Apr 12 '12 at 07:41
  • 37
    No, it is the same. `LIMIT 8, 18` is just a shortcut :) – Mosty Mostacho Apr 12 '12 at 07:42
  • 1
    Best answer as i was also confused i was thinking that it returns the data between the two digits but now its clear :) – MR_AMDEV Sep 16 '18 at 15:09
  • But be careful with `LIMIT 8, 0`; if we compare `SELECT * FROM table LIMIT 8, 0` to `SELECT * FROM table LIMIT 8 OFFSET 0`, the first one will not select 8 records as you may expect. Instead, it will return `0` records as **limit construction** is the following `LIMIT [offset,] row_count` [see here](https://www.mysqltutorial.org/mysql-limit.aspx) but the second (the one with the `OFFSET` keyword) example will return `8` records. Of course, if you have these records in your DB. – Utmost Creator Oct 03 '21 at 16:44
87

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
TheKronnY
  • 183
  • 1
  • 13
vineet
  • 13,832
  • 10
  • 56
  • 76
4

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

Mahesh Patil
  • 1,529
  • 1
  • 11
  • 22
1

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.

w.Daya
  • 443
  • 1
  • 7
  • 12
0

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.

A Pali
  • 1