andrei_1234567890 andrei_1234567890 - 5 months ago 10
SQL Question

SQL Starting with columns

I have:

Id | Timestamp column
---------------------------
1 | 700 (not ten, but simple to read)
2 | 800
3 | 800
4 | 800
5 | 600


I get the first 2 records, ordered by timestamp DESC

Id | Timestamp column
---------------------------
2 | 800
3 | 800


Now, I can't get the next 2 records ordered by timestamp DESC starting with id 3 and timestamp 800

I try:

SELECT * FROM table WHERE timestamp < 800 ORDER BY timestamp DESC LIMIT 2


but returns Id 1 and 5, which is not correct.

Try it also:

SELECT * FROM table WHERE timestamp <= 800 ORDER BY timestamp DESC LIMIT 2


but returns id 2 and 3, which is again not correct.

Try it also

SELECT * FROM table WHERE timestamp <= 800 AND id > 3 ORDER BY timestamp DESC LIMIT 2


but returns Id 4 and 5, which is again not correct.

And so on.... all I try is not working.

The query should return exactly:

Id | Timestamp column
---------------------------
4 | 800
1 | 700


I also try to play with order by id, but I can't get it work.

Some help ?

EDIT: I can't explain, but for sure I don't know how many records are already extracted, so I can't use Offset. I just know the last record - id 3 and timestamp 800...

Answer

You need to order by both timestamp and id, so that you get consistent ordering within all the rows with the same timestamp. Then when you do the next query, you use the last id as part of the WHERE clause.

So the first time it's

SELECT *
FROM table
ORDER BY timestamp DESC, id
LIMIT 2

Get the ID and timestamp from the last row of this into @id and @timestamp, then your next query should be:

SELECT *
FROM table
WHERE (timestamp = @timestamp AND id > @id) OR (timestamp < @timestamp)
ORDER BY timestamp DESC, id
LIMIT 2

DEMO

Comments