I have a table :
EventLog (
EventID (INT),
UserID (VARCHAR(50),
Event (NTEXT),
EventDate(DateTime),
DocuvmentID(INT)
)
I need to write a query to get the latest event, datetime for a bunch of userId which will be
WHERE UserID IN ( 'john','tom'...etc)
How can I do that?
SELECT y.UserID, y.Event, y.EventDate
FROM (SELECT UserId, MAX(EventDate) AS MaxDate
FROM YourTable
WHERE UserId IN ('john','tom',...)
GROUP BY UserId) t
INNER JOIN YourTable y
ON t.UserId = y.UserId
AND t.MaxDate = y.EventDate
With a simple CTE:
;WITH LatestDates AS
(SELECT
EventID, UserID,
Event, EventDate,
DocumentID,
ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY EventDate DESC) AS 'RowNum'
)
SELECT *
FROM LatestDates
WHERE RowNum = 1 AND UserID IN (........)
This partitions your data by some criteria (I picked UserID as an option - might be something else for you), then numbers each group sequentially starting at 1, ordered by another criteria (here: EventDate DESC) - so the most recent event for each "partition" has RowNum = 1 which is what I select from that CTE
select
UserID,
MAX(EventDate) AS LatestEventDate
from
EventLog
where
UserID in ('john','tom')
group by
UserID
