SQL Server rows with latest date

I have a table :

EventLog (
    EventID (INT),
    UserID (VARCHAR(50),
    Event (NTEXT),
    EventDate(DateTime),
    DocuvmentID(INT)
)

I n开发者_高级运维eed 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