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;
