This seems like a fairly straight forward and simple query, however you run into the max row information problem as described in the manual at
http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html Finding the first part is fairly easy and straight forward, it is just getting the price at the same time as the other info where you run into that problem. Here is the query I wrote using the max-concat trick as described at the bottom. It would be simple to use the temp table one as well. select tbl1.timestamp, left(max(concat(lpad(tbl2.timestamp,8,'0'),tbl2.price)),8) as timestamp, tbl1.code, substring(max(concat(lpad(tbl2.timestamp,8,'0'),tbl2.price)),9) as price from tbl1, tbl2 where tbl1.timestamp >= tbl2.timestamp and tbl1.ordertype='N' and tbl1.code=1111 and tbl2.code=1111 group by tbl1.timestamp; That should give you the results you are looking for. As for speed the temp table one might be faster depending on the number of rows you have and such. You might want to do some testing to find out. The reason we don't need to use the concat trick on the tbl1.code in the select string is because that is specified in the where clause and can only be that singular value. On a different topic, does anyone know if it would faster to use the "tbl1.code = 1111 and tbl2.code = 1111" or to use "tbl1.code = 1111 and tbl1.code=tbl2.code"? I've always assumed it was the first, but depending on how the query processor works I could see the second one being faster. If anyone knows for sure, feel free to help me out, Thanks. Harrison ----- Original Message ----- From: "Patelli Paolo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 21, 2002 9:49 PM Subject: SQL query > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > I have a simple SQL problem: I have two tables (two different log files): > > tbl1 > TimeStamp, Code, OrderType > ... > 10:22:01, 1111, "N" > 10:23:02, 1111, "N" > 10:23:19, 1111, "N" > 10:24:11, 1222, "N" > 10:25:11, 1111, "D" > 10:27:33, 1111, "N" > ... > > tbl2 > TimeStamp Code Price Size > ... > 10:21:00, 1111, 300, 10 > 10:22:20, 1555, 221, 10 > 10:22:30, 1111, 312, 10 > 10:23:41, 1111, 353, 10 > 10:24:56, 1222, 156, 30 > ... > > for each row of tbl1 that satisfy for example the condition OrderType="N" > and Code=1111 I would like to extract tbl2.Price. The price that I want to > select has a tbl2.TimeStamp that is <= tbl1.TimeStamp i.e. given an event in > tbl1 I want the price relative to the previous event recorded in tbl2. > The query output that I want looks like: > > tbl1.TimeStamp, tbl2.TimeStamp, tbl1.Code, tbl2.Price > 10:22:01, 10:21:00, 1111, 300 > 10:23:02, 10:22:30, 1111, 312 > 10:23:19, 10:22:30, 1111, 312 > 10:27:33, 10:23:41, 1111, 353 > > Any suggestion? > > Paolo > > > - -- > ======================================================== > Paolo Patelli > ======================================================== > Actual Position: > Graduate fellow at Santa Fe Institute > 1399 Hyde Park Rd, Santa Fe, New Mexico 87501 USA > ph.: 505 984 8800 ext. 293 > email: [EMAIL PROTECTED] > ======================================================== > PhD student, S. Anna School for Advanced Studies > via Carducci 40, 56100 Pisa, Italy > email: [EMAIL PROTECTED] > ======================================================== > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE86vlHk+lyphfv8fURAiI7AJsFz2cbZXSd69skKYUaJ3UpNlKxyACgwOAS > QtE415Io+lXwTQaXlYfixoM= > =id5e > -----END PGP SIGNATURE----- > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
