D'oh. Very good. I wish I'd thought of that.
In response to Michael DePhillips' point about the UDF - I believe
that in MySQL 5.x UDFs can't query tables. In Oracle, SQL Server,
etc. they can and I'm sure they will in the future.
Douglas Sims
[EMAIL PROTECTED]
On Aug 14, 2006, at 10:17 AM, [EMAIL PROTECTED] wrote:
Here's a single query version of Douglas's solution:
select @id:=6;
select distinct t.testid,
(select max(testid) from t where testid <@id) as previousId,
(select min(testid) from t where testid > @id) as nextId
from t
where [EMAIL PROTECTED]
Donna
Douglas Sims <[EMAIL PROTECTED]>
08/14/2006 10:47 AM
To
Michael DePhillips <[EMAIL PROTECTED]>
cc
Dan Julson <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Subject
Re: Query Question
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]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may
violate
applicable laws. If this email has been received in error, please
notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not
necessarily
represent the views of IntelliCare.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
