CODEWITHSUNDEEP
mysqlsql
Rakushoe
Rakushoe

Reputation: 116

Jumping the selection of value by 3 regardless of the id name

I am trying to retrieve the values using query SELECT * FROM `table` ORDER BY `cid` ASC LIMIT 3

But what happens is id don’t seem to get it to retrieve from 5 to 7, 8 to 9 or 10 to 12. I CANNOT USE the query WHERE `time` = 5 or something since the values from cid constantly changing. Can this be done? just jumping the retrieval of values by 3?

enter image description here

Upvotes: 0

Views: 31

Answers (1)

jarlh
jarlh

Reputation: 44776

LIMIT has an offset option as well: LIMIT offset, count, e.g.:

SELECT * FROM `table` ORDER BY `cid` ASC LIMIT 6, 3

However, if someone deletes the row with cid = 3 after you've read row 1 to 3, you will never read the row with cid = 5.

I'd go with an adjusted version of your original query, keeping track of the highest cid from each SELECT, and use that value the next time you SELECT 3 more rows.

SELECT * FROM `table`
WHERE cid > "previous_highest_cid"
ORDER BY `cid` ASC LIMIT 3

Will probably be faster, you won't miss any rows etc.

Upvotes: 2

Related Questions