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 <[EMAIL PROTECTED]> 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
