SELECT non-duplicate records joined to table with duplicate date records, by last date

Question

The title probably doesn't make much sense, so I'll try to be descriptive here in the subject.

Consider 2 tables in MSSQL2005:

Cases table:

id int,
caseNo string

Events table:

id int,
caseID int,
eventDate date/time

I need a select statement for a view which will return single rows of: cases.caseNo, events.eventDate (date part only)

They are related/joined by events.caseID = cases.id many to one. There are multiple event records per case records. I want the resultset to be single caseNo with the latest/most recent value of events.eventDate.

Thanks in advance.

Answer 1

You can just use the max function to grab the latest date, like so:

select
    c.caseNo,
    max(e.eventDate) as eventDate
from
    cases c
    inner join events e on
        c.id = e.caseid
group by
    c.caseNo

Answer 2

There are 5 ways to do this, these are described here: Including an Aggregated Column's Related Values

Basically something like this if you need more than just the date and the case id

select e.*,c.*
from(
select caseID,max(evendate) as MaxEventDate
from Events
group by caseID) x
join Cases c on c.Id = x.caseID
join Events e on e.eventDate =  x.MaxEventDate
and e.caseID = x.caseID

otherwise just group by id and use max for date

select  max(e.EventDate) as MaxEventDate,
    c.CaseNo
from
    Cases c
    join Events e on
        e.caseid = c.id 
group by c.caseNo