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

Oops, I only did half the requested query.

[(Anonymous)]

I simply assumed we know the desired row's key before we make the query,
whereas you want the desired row's key to be discovered within the same query.

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

[(Anonymous)]

This should work in one query of pure SQL (no stored procedures)
but the efficiency is somewhat questionable. Also, it's like
a code comedy.

=========
sqlite> -- The key *directly* sought (without the next or prev rows)
sqlite> select key from pageviews where url like '%elf%' and key
...> > 10000
...> order by key asc limit 1;
10198
sqlite> -- Finds the key *directly* sought, also prev and next existing keys.
sqlite> 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 =
...> (select MAX(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 =
...> (select MIN(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)
...> ;
10198
10197
10199
sqlite>
=========

--Cat Typist

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

[(Anonymous)]

Here it is in a more copy-and-pasteable form

-- The key *directly* sought (without the next or prev rows)
select key from pageviews where url like '%elf%' and key > 10000
  order by key asc limit 1;


-- 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 =
      (select MAX(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 =
      (select MIN(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)
;

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)
;