List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 30 2002 8:09pm
Subject:Re: SQL help plz
View as plain text  
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;
Thread
SQL help plzP.Agenbag30 Jan
  • Re: SQL help plzPaul DuBois30 Jan
  • Re: SQL help plzSteve Severance31 Jan
RE: SQL help plzRick Emery30 Jan
RE: SQL help plzRick Emery30 Jan
RE: SQL help plzRick Emery30 Jan