From: Peter Brawley
Date: November 12 2008 3:08pm
Subject: Re: Row before and after?
List-Archive: http://lists.mysql.com/mysql/215187
Message-Id: <491AF156.9010500@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060407080906040500010708"

--------------060407080906040500010708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Micah,

>I'm trying to find the first row before and 
>the first row after a specific row

Here's one way:

drop table if exists t;
create table t(userid int, data int);
insert into t values(1,10),(3,20),(6,30),(8,50),(10,60), (13,80);

-- retrieve rows just before and just after userid=8:
select t.*
from t
join (
  select
    (select max(userid) from t where userid<8) as prev,
    (select min(userid) from t where userid>8) as next
  from t
  where userid=8
) a
on t.userid=a.prev or t.userid=a.next;
+--------+------+
| userid | data |
+--------+------+
|      6 |   30 |
|     10 |   60 |
+--------+------+

PB

-----

Micah Stevens wrote:
> Select the UserId one less, and then ORDER ASC LIMIT 3.
>
> Assuming your UserId's are sequential, it's easy, given userID X
>
> SELECT * FROM Users WHERE UserId = X-1 ORDER BY UserId ASC LIMIT 3;
>
> If they're not sequential due to deletions, etc, it becomes a bigger
> problem. You could do a subquery, but that would only be marginally
> faster than two queries.
>
> Sorry if I'm not more creative in the morning. :)
>
> -Micah
>
> On 11/12/2008 01:10 AM, Waynn Lue wrote:
>   
>> Whoops, just realized I made a mistake in the examples.  What I'm really
>> looking for is these two queries:
>>
>> SELECT * FROM Users WHERE UserId > *userid*;
>> SELECT * FROM Users WHERE UserId < *userid*;
>>
>> Waynn
>>
>> On Wed, Nov 12, 2008 at 12:14 AM, Waynn Lue <waynnlue@stripped> wrote:
>>
>>   
>>     
>>> I'm trying to find the first row before and the first row after a specific
>>> row.  Essentially I want to do these two queries, and get each row.
>>>
>>> SELECT * FROM Users WHERE UserId = <userId> ORDER BY UserId DESC LIMIT 1;
>>> SELECT * FROM Users WHERE UserId = <userId> ORDER BY UserId LIMIT 1;
>>>
>>> Is there any way to combine this into one query?  OFFSET doesn't allow a
>>> negative number, which is essentially what I want.
>>>
>>> Thanks,
>>> Waynn
>>>
>>>     
>>>       
>>   
>>     
>
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
>
>   

--------------060407080906040500010708--