Selecting range of records with MySql

Question

This is my query:

SELECT  @curRow := @curRow + 1 AS row_number,
    myTable.id
FROM    myTable
JOIN    (SELECT @curRow := 0) r

This gives me result with all the records in myTable. Ex.

row_number    id
---------- -------
    1         100
    2         101
    3         102
    4         103
    5         104
    6         105
    7         105
    8         106
    9         107
   10         108
   11         109
   12         110
   13         111
   ...

What if I need to select only rows between 6 to 10? Selecting 1 to 5 is easy with LIMIT 5, but how about selecting range of rows in between?

Answer 1

SELECT  @curRow := @curRow + 1 AS row_number, myTable.id
   FROM myTable LIMIT 6, 5; 

This will show records 6, 7, 8, 9, 10

Then you get output like

row_number    id
---------- -------
    6         105
    7         105
    8         106
    9         107
   10         108

Answer 2

SELECT  @curRow := @curRow + 1 AS row_number,
    myTable.id
FROM myTable  LIMIT 5 OFFSET 6;

Use the OFFSET with the limit.

LIMIT decide that how much rows will come after query execute and offset decide that from which records the records will be filter.

Read this tutorial for offset.