Microsoft SQL Query - Return last record from second table

Question

I have 2 tables which I am wanting to run a query on. The first table is dbo.Incidents which contacts the primary key IncidentID. The second table is dbo.IncidentActions which has the primary key ActionID and has a field IncidentID which links with the first table.

There are many actions with the same IncidentID and I am wanting to return 1 row only per IncidentID with the last ActionID for that IncidentID.

Thanks Andomar - nearly there I promise :)

select  *
from    (
        select  i.IncidentID
        ,       ia.ActionID
        ,      RIGHT('' + CAST(DATEDIFF(mi, ia.ActionTime, CONVERT([varchar], GETDATE(), 14)) 
                         / 60 % 60 AS VARCHAR), 2) + ' hr(s) ' + RIGHT('' + CAST(DATEDIFF(mi, ia.ActionTime, CONVERT([varchar], GETDATE(), 14)) % 60 AS VARCHAR), 2) + ' min(s)' AS LastActionTime

        ,       row_number() over (
                    partition by i.IncidentID 
                    order by ia.ActionID desc) as rn
        from    dbo.Incident i
        join    dbo.IncidentAction ia
        on      i.IncidentID = ia.IncidentID
        ) as SubQueryAlias
where   rn = 1

This is all working now I just want to set Where ia.ActionDate = GetDate() - can't seem to get that working

Answer 1

If you're just looking for the top ActionID per incident:

select  i.IncidentID
,       max(ia.ActionID) as MaxActionIdForIncident
from    Incidents i
join    IncidentActions ia
on      i.IncidentID = ia.IncidentID
group by
        i.IncidentID

If the IncidentActions table has a timestamp column you'd like to use to determine which row to return, you could use the row_number() window function:

select  *
from    (
        select  i.IncidentID
        ,       ia.ActionID
        ,       ia.ActionByUser -- Note: now you return any column
        ,       row_number() over (
                    partition by i.IncidentID 
                    order by ia.ActionTimestamp desc) as rn
        from    Incidents i
        join    IncidentActions ia
        on      i.IncidentID= ia.IncidentID
        ) as SubQueryAlias
where   rn = 1 -- Latest action per incident only

The subquery is required because you can't use window functions in the where clause. For more examples, browse the greatest-n-per-group tag.