Greatest Date

[gizmo1973]

I have a query that produces a list of dates for a specific event, the company has now decided that they will repeat the event until they get a satisfactory conclusion...
So I need to be able to only show the last event by the date entered and that's where I am stuck, I have tried to convert to a value using Year(date) and then using highest but that only give the highest overall not for each record
i.e

Fred Blogss & Co 1/7/06
Fred Blogss & Co 19/7/06
Fred Blogss & Co 21/7/06
Joe Blogs Co 1/8/6
Joe Blogs Co 9/8/6

The two in bold would be the only two I want to see and so on
Any ideas?????

Regards, Phil.

I'm here because I LOOK busy

The New Red Army Marches On

http://www.fcunited.co.uk

P.S. Beer anyone?

 

[r937]

select event
     , someothercollumn
     , eventdate
  from daTable as T
 where eventdate
     = ( select max(eventdate)
           from daTable
          where event = T.event )

usually you can simple do a GROUP BY with MAX() if all you want is the key (event) and latest eventdate per key, but the query above allows you to extract additional columns in the row with the latest eventdate

r937.com | rudy.ca