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

Re: Now ALL IN ONE QUERY! Also, pretty comical IMHO.

[(Anonymous)]

This works too, but it's a little less funny, IMHO.

-- Finds the key *directly* sought, also prev and next existing keys.
select key from pageviews 
  where key in
        (select key from pageviews where url like '%elf%' and key >10000
          ORDER BY key ASC LIMIT 1)
    -- find prev key
    or key in
      (select key from pageviews where 
        key < 
        (select key from pageviews where url like '%elf%' and key >10000
          ORDER BY KEY ASC LIMIT 1)
      ORDER BY key DESC LIMIT 1)
    -- find next key
    or key in
      (select key from pageviews where    
        key > 
        (select key from pageviews where url like '%elf%' and key >10000
          ORDER BY KEY ASC LIMIT 1)
      ORDER BY key ASC LIMIT 1)
;