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