[thelist] An sql problem

rudy r937 at interlog.com
Fri May 4 16:47:05 CDT 2001


> What sql sould I use to get the next and previous id's
> based on a particular "id", and ordered in this case by "ref"?

hi philippe

assume you have the value of the current row's ref field in a variable -- i
apologize that i do not know php sytnax, if that's what you're using, so i
will "fake it" by using %ref% to represent this

previous --

   select id, ref, cat
     from yourtable
    where ref =
          (select max(ref)
             from yourtable
            where ref < %ref% )

next --

   select id, ref, cat
     from yourtable
    where ref =
          (select min(ref)
             from yourtable
            where ref > %ref% )

note that you would have to be ready to accept an empty result set if you
were already positioned on the lowest ref and asked for the previous, or
positioned on the highest and asked for the next

unfortunately, mysql does not allow subselects, according to the
documentation (watch for wrap) --

http://www.mysql.com/documentation/mysql/bychapter/manual_Compatibility.htm
l#Missing_Sub-selects

note this sentence -- "For more complicated subqueries you can often create
temporary tables to hold the subquery."

rather than do this, i would just run the extra query  (stop that hooting
in the back, there)

previous --

   select max(ref)
     from yourtable
    where ref < %ref%

   save max(ref) as %prev%

   select id, ref, cat
     from yourtable
    where ref = %prev%

next --

   select min(ref)
     from yourtable
    where ref > %ref%

   save min(ref) as %next%

   select id, ref, cat
     from yourtable
    where ref = %next%

note that you run one extra query at the time the previous/next is
requested, not, as anthony seemed to imply, two extra queries whenever
getting a row


oh, and all of the above queries will run a lot more efficiently if you
define an index on the ref column

one final caveat:  beware duplicate ref values in your data (so make the
index on ref unique) or you will get multiple rows for previous or next at
some point


rudy.ca





More information about the thelist mailing list