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.
