I think this will do it, although it takes three queries.
I'm assuming the id values are unique, even if there can be gaps
(that's what you might get with an AUTO_INCREMENT field). If the
values are not guaranteed to be unique then this may not give what
you want (if there are multiple rows with the same value you're
looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would
give you 3,5,6, not three fives.)
SELECT @id:=5;
SELECT * FROM t WHERE id<(SELECT MAX(id) FROM t WHERE id<@id) ORDER
BY id DESC LIMIT 1;
SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1;
SELECT * FROM t WHERE id>(SELECT MIN(id) FROM t WHERE id>@id) ORDER
BY id ASC LIMIT 1;
But as to putting that in one statement... it might be better just to
do it as three.
Douglas Sims
[EMAIL PROTECTED]
On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote:
Hi Dan,
Thanks for the prompt reply,
As I described it yes, you are correct, however, the id may not
always be one(1) value away. So the number one needs, somehow, to
be replaced with a way to get the "next largest value " and the
"previous less than" value.
Sorry for the lack of precision in my prior post.
Regards,
Michael
Dan Julson wrote:
Michael,
I would think this is what you want.
Select ID from T1 where ID BETWEEN (<id in question> - 1) and (<id
in question> + 1)
If you want distinct values, place the distinct keyword in front
of ID (i.e. Select DISTINCT ID...
This should do it for you.
-Dan
Hi,
Does anyone have a clever way of returning; a requested value
with one
value less than that value, and one value greater than that value
with
one query.
For example T1 contains
ID
1234
1235
1236
1238
select ID from T1 where ID = 1235 and ID<1235 and ID >1235 LIMIT 3
(obviously this doesn't work) I would want to return....
1234
1235
1236
or;
select ID from T1 where ID = 1237 and ID<1237 and ID >1237 LIMIT 3 I
would want
1236
1238
I would be surprised if there was no way of doing this.....but then
again, I'm often surprised....
Thanks
Michael
--
Michael DePhillips
www.star.bnl.gov
--
Michael DePhillips
www.star.bnl.gov
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
