Re: How to select a row and an adjacent row?

At 10:55 AM 23-06-2000 +0400, you wrote:
>Fri, Jun 23, 2000 at 02:21:17PM +0800, Lincoln Yeoh:
>> Just say I'm doing a select ... from ... where ... order by...
>> I know the row I want, but I also want the row after (or before in another
>> case), and the row I want is NOT at the top/bottom of the order by :(.
>
>You could probably do this:
>
>select * from yourtable where <key> > <known_row_key>
>    order by <key> limit 1;
>
>- isn't it?

Thanks. Looks like that would work. Dunno why previously I thought that
wouldn't have worked- fuzzyminded today I guess.

More info: in my case I have a list of stuff - say the fields are:
messageid, address, date, subject

Messageids are unique but not adjacent. But the other fields can have
duplicates. When I do "order by" I include the messageid as well, this
probably allows the method to work.

So I could try something like:
(if list was ordered by date and messageid)
select * from table where criteria=xxxxx and messageid > currentmessageid
    order by date, messageid limit 1;

or if ordered by descending date and messageid:
select * from table where criteria=xxxxx and messageid < currentmessageid
    order by date desc, messageid desc limit 1;

Similar for subject and the other stuff.

If I'm looking for previous and next I'll have to do the two selects.
They're limited to one row each, but I wonder if that would still be faster
than a full select.

Thanks,

Link.