Offset with Primary Key

[sdleihssirhc]

I have a row's primary key. I would like to select all of the data for both this row and the one right after it. I think I'll be using syntax similar to...

SELECT * FROM `some_table` ORDER BY `some_column` LIMIT x, 2

But, from what I understand, LIMIT uses offsets. Is there some way to take my primary key and turn it into an offset? Bonus if I don't have to run a second query.

[r937]

could you explain "right after it" a little more?

you want the row with the next higher PK value?

[Dr John]

select * from table where PK >=$yourPK, order by PK Limit 2

Assuming you mean the next PK whose value is greater than the one you know.

however what significance does this next record have???

PK values could be 7,8,9, 19, 45, 82, 83, 90
i.e. there will be gaps due to deletions. and row with pk 19 has no obvious relationship to the row with pk 45.

[felgall]

If you don't mean the next higher primary key then what ORDER BY are you using to define the order of the records so that next actually has a meaning (the records in the database have no specific order so the next record in the database could be any of the records and could easily change over time)

[sdleihssirhc]

Yeah, that was terrible wording on my part.

Dr John's suggestions is exactly what I was looking for. I have an input date with each record, so I ordered everything by that, and then chose "WHERE `id` >= x"

But that doesn't seem right, now that I've done it and thought about it. The page is set up to display a certain entry, and then (at the bottom of the page) display a link to the next-oldest entry. All I have to start with is the primary key for the current entry (via a GET variable). So, basically, the way I'm doing it now only works if the primary key goes in the same direction as the input date (that is, newer entries also have higher primary key values).

[r937]

next oldest...

SELECT stuff FROM daTable
WHERE inputdate < ( SELECT inputdate FROM daTable WHERE id = $current )
ORDER BY inputdate DESC LIMIT 1