SQL experts apply within, driving me insane

[SiD the Turtle]

So I have some data like this:

id | date
-- | -----------
01 | 01-01-2006
02 | 02-06-2006
03 | 07-06-2006
03 | 01-09-2006
04 | 05-10-2006

As you can see the id is not unique, for example id 03 has two date values. I want to find one record for each of the ids, but finding the newest date for ids with more than one row. So I'd end up with:

id | date
-- | -----------
01 | 01-01-2006
02 | 02-06-2006
03 | 01-09-2006
04 | 05-10-2006

Any ideas?

 

[NumptyUK]

something like

select max(Date) from <tablename>
group by ID, Date


i know theres a max and a group by in there somewhere to get it working

 

[SiD the Turtle]

Nice one, it worked brilliantly! Always used max just to give me the maximum of a whole table, rather than of a set of results.