At 21:36 +0200 1/30/02, P.Agenbag wrote: >Hi >I need help with an sql string: > >have a table similar to this: > >id key name date >1 123 name1 date1 >2 123 name1 date2 >3 111 name2 date1 >4 111 name2 date2 >5 123 name1 date3 >Now, I need sql to report the following result: > >name1 date3 >name2 date2 > > >thus, report all the names in the table but only the ones with the >latest date. > >The sql MUST use the key to compare the different rows with each >other and NOT the name, as the name is prone to spelling errors (ok, >the key as well, but it's easier to make a typo with letters than >with digits and I already have a couple of entries where the names >of the entries are different.
Create another table to hold the maximum date associated with each key value (which I'm calling k rather than key because key is a reserved word): CREATE TABLE t2 SELECT k, MAX(date) AS date FROM t GROUP BY k; Then join this table with the original to get the rows with the appropriate k and date values, printing out the name and date values: SELECT t.name, t.date FROM t, t2 WHERE t.k = t2.k and t.date = t2.date ORDER BY name; --------------------------------------------------------------------- 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
