Yes, you're right, Jerry. Thing is that I can NOT use 'min_t' in 2) query, but have to plug in the actual value from 1) query. This isn't what I want.
If I tried this: 1) create temporary table tbl_min as (select min(abs(Ta - timestamp)) as min_t from B; so tbl_min is like: -------- min_t | -------- 0.00012 | --------- 2) then, I say: select min_t, * from tbl_min, B where (timestamp + min_t) = Ta or (timestamp - min_t) = Ta; then I will get the record as: ---------- min_t | other fields ----------- you see, the field 'min_t' isn't necessary there. Wait, if I say: select * from tbl_min, B where (timestamp + min_t) = Ta or (timestamp - min_t) = Ta; Yes, I got the result without 'min_t' in it. But this solution still used more than 1 query and used a temporary table to hold the imtermidiate value. Any better solution? Thanks. > Your first query returns a scalar (single value), right? You can put its > value into a variable, and use that in the second query. It's not exactly > what you wanted, but it will work without external programming. > > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > -----Original Message----- > From: Xiaobo Chen [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 26, 2006 10:09 AM > To: [EMAIL PROTECTED] > Cc: mysql@lists.mysql.com > Subject: Re: Help for query > > I found if I divided into 2 steps, I will find the record in table B: > > Ta - the given timestamp from table A; > > 1) select min(abs(Ta - timestamp)) as min_t from B; > > 2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t = > Ta); > > But, how can I make these 2 steps into 1 query? > > Thanks. > > Xiaobo > >> Hi, all >> >> The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation. >> There are 2 tables something like these: >> >> Table A: >> ---------------------------------- >> location|timestamp | other fields >> ------------------------------- >> >> >> Table B >> >> -------------------------------- >> location|timestamp | other fields >> --------------------------------- >> >> (location. timestamp) make the 'primary key' for each table. >> >> The task is that: the locations are the same, given a timestamp from >> table >> A, I need to find the record in table B which has the closest timestamp >> as >> the given one in table A. >> >> I checked the book and research the internet, but didn't find a hint or >> solution yet. Could any one give me a hint please? >> >> Thanks in advance. >> >> Xiaobo >> >> >> -- >> 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] > > > > > > -- > 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]
