Microsoft SQL Query - return the last record from the second table
I have 2 tables on which I want to run a query. The first table dbo.Incidents
that is associated with the primary key IncidentID.
The second table dbo.IncidentActions
that has a primary key ActionID
and has a field IncidentID
that is associated with the first table.
There are many actions with the same IncidentID
and I want to return 1 row only IncidentID
with the last one ActionID
for this IncidentID
.
Thanks Andomar - almost 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 all works now, I just want to set Where ia.ActionDate = GetDate () - it can't seem to work
source to share
If you are just looking for the top one 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 table IncidentActions
has a timestamp column that you want to use to determine the row to return, you can use the window function row_number()
:
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 cannot use window functions in a sentence where
. Check out the greatest-n-per-group tag for more examples .
source to share