Group by ID having MAX(date) problem

Databases
1

I have a table such as the following:

ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Smith   | 2008-01-01
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Thorn   | 2008-01-05
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20

Basically, I want this to return the most recent name for each ID, so it would return:

ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20

The query (and variations of) I’ve been trying, and failing with is:

SELECT DISTINCT ID, Forename, Surname
FROM Name
GROUP BY ID
HAVING MAX(Created)
2 
SELECT ID, Forename, Surname
  FROM Name [COLOR="red"]AS t[/COLOR]
 WHERE Created =
       ( SELECT MAX(Created)
           FROM Name
          WHERE ID = [COLOR="red"]t[/COLOR].ID )
3

Since forename and surname are neither grouping coulmn nor aggregate functions, using them in the select clause is not valid. Here’s how i would do it :


SELECT id, forename, surname, created
FROM name n
WHERE created = (SELECT MAX(created)
                              FROM name
                              GROUP BY id
                              HAVING id = n.id);

Edit: should have refreshed the thread before answering, Use rudy’s query, it is more efficient, replacing group by and having clause with a where clause should be more efficient.

4

Thanks to both of you. It’s working perfectly now!