80 rows of data , first select 1-20 rows , second select 21-30 rows

Question

I have a table containing rows with timestamped.

Normally if I want to get the latest 20 rows out according to the time. I use:

$sql = "SELECT * 
    FROM comment 
    ORDER BY time DESC 
    LIMIT 20";

But now, I want to get the latest comments AFTER the latest 20 rows and LIMIT to 10. That means rows 21-30.(of course , everything is according to timestamp)

How can I do that using MySQL?

Answer 1

MySQL has a built-in offset that you can use with LIMIT:

$sql = "SELECT * FROM comment ORDER BY time DESC LIMIT 10, 20";

Also, refer to this SO post: MySQL LIMIT/OFFSET: get all records except the first X

Answer 2

$sql = "SELECT * FROM comment ORDER BY time DESC LIMIT 20, 10";

Answer 3

Hope it will select from 21 to 30 records

sql = "SELECT * FROM comment ORDER BY ID DESC LIMIT 20, 10";

Answer 4

Try a mixture of limits

$sql = "select * from (SELECT * FROM comment ORDER BY time DESC LIMIT 30) as A order by time ASC limit 10";

The mysql built in offset method others have posted looks better though.

Answer 5

There are two options:

• Get 30 rows and use PHP to split the result set into a group of 20 and a group of 10.
• Send two queries, one for 20 and one for 10 rows.