Anthony, Is all the information you need to retrieve in one table? If yes , I don't think you have to do join to get the row you want..
you should be able to do it by: SELECT columns FROM table WHERE id=yourid AND DATE => 'date' ORDER BY DATE LIMIT 1; Is this what you are asking? And I think it would better suite you if you choose DATETIME or TIMESTAMP datatype for your date column if you have frequent insertions. See info about them at: http://www.mysql.com/doc/D/A/DATETIME.html Gurhan -----Original Message----- From: Anthony R. J. Ball [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:30 AM To: [EMAIL PROTECTED] Subject: Getting the most recent record per id before date X I am trying to figure out the best way to select the row of data for a specific id that is the most recent entry on or before a specific date. I realize that I could create a temp table and select where date <= X and group by id, inserting the max date into the table with the id, then do a join... but I am wondering if the thought I had would be a really nasty sneaky trick to do it in one query... As far as I can tell, group by returns the first row as it is ordered by the key? If this is true would it be possible to design the keying for this special case, such that the field I wanted would rise to the top in a normal group by? or am I being a heretic and shold buy a stone proof suit? Just one of those things I have to check up on... :) -- ___ __ __ __ _ _ ____ _ _ ____ ____ / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) \__ \ )(__)( /(__)\\ / )__) ) ( )__) )( (___/(______)(__)(__)\/ (____)()(_)\_)(____) (__) Guess it's time for Plan B, huh? --------------------------------------------------------------------- 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
