[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