CODEWITHSUNDEEP
mysql
Blank
Blank

Reputation: 4695

Select records after ID

I have a MySQL table which contains the following two fields (There are others, but they're irrelevant)

creation_time | object_id  
1325384907    | 75b31b2c-78e9-46b9-afc8-6d90c0df2ee6
1325388538    | c87734c9-5109-4956-a1cb-7e719ebb4549
1325430911    | 79fe7f41-4a73-4aa3-b1e4-68be0253d9ab
[...]

What I would like to do is select from the table, ordered by time, all objects after a certain object_id

So the sort of thing I'd want is a query like

SELECT * FROM MYTABLE ORDER BY creation_time ASC WHERE object_id > 'c87734c9-5109-4956-a1cb-7e719ebb4549'

Which should return 1325430911 | 79fe7f41-4a73-4aa3-b1e4-68be0253d9ab (Being the next record after that object ID).

This is for a paging system (I.E. 'get results after'), the only information we have is the uuid so we can't just select creation_time > x.

Upvotes: 0

Views: 2261

Answers (2)

John Woo
John Woo

Reputation: 263703

SELECT Creation_Time, Object_ID
FROM `tableName`
WHERE Creation_Time > 
        (SELECT Creation_Time 
         FROM `tableName`
         WHERE Object_ID = 'idhere')
ORDER BY Creation_Time

Upvotes: 0

Leandro Bardelli
Leandro Bardelli

Reputation: 11578

SELECT * FROM MYTABLE 
WHERE CREATION_TIME > (SELECT CREATION_TIME FROM MYTABLE WHERE OBJECTID='YOUR_OBJECT_ID_THAT_YOU_REALLY_KNOW' LIMIT 1) 
ORDER BY CREATION_TIME ASC

Please be aware of LIMIT 1 because if subquery returns more than 1 result, the query will be fail

Upvotes: 2

Related Questions