Oh, Mighty LJ Brain: SQL for "next" and "previous" entries?

[elfs]

Oh, mightly LJ brain, riddle me this:

I have a table in a MySQL (well, okay, it's Sqlite3 right now, but it'll my MySQL in production) database, and the primary keys are known to not be contiguous. That's important.

It's possible to construct a WHERE clause giving you a single item, if that items qualties are unique enough. Is it possible, using some magic combination of WHERE and ORDER BY and something I don't know, to say "give me unique item N, and using the rule of the ORDER BY clause, also give me the next and previous objects?"

Comments

[shockwave77598.livejournal.com]

Why not have additional fields that hold the keys for the previous and next items? A database implementation of a linked list.

[elfs.livejournal.com]

Insertions may be random. It might be worth contemplating, but it makes insertions expensive.