How can I write a query that will give me all the rows of a particular id that have the latest date filtered by another column?

For example. I want all records that have the latest date where name equals Lily and Al

From this table:

The result of the query should be:

Thanks!

CodePudding user response：

Something like this?

DECLARE @tbl table (ID int, [Date] datetime, [name] nvarchar(100));

INSERT INTO @tbl(ID, [Date], [Name])
VALUES (111, '2022-08-24 20:56:54','Lily')
    ,(111, '2022-08-24 20:56:54','Lily')
    ,(111, '2022-08-24 20:56:54','Lily')
    ,(222, '2022-08-30 17:47:16','Lily')
    ,(222, '2022-08-30 17:47:16','Lily')
    ,(222, '2022-08-30 17:47:16','Lily')
    ,(222, '2022-08-30 17:47:16','Lily')
    ,(333, '2022-08-26 06:29:59','Stan')
    ,(333, '2022-08-26 06:29:59','Stan')
    ,(333, '2022-08-26 06:29:59','Stan')
    ,(333, '2022-08-26 06:29:59','Stan')
    ,(444, '2022-09-08 18:17:11','Al')
    ,(444, '2022-09-08 18:17:11','Al')
    ,(444, '2022-09-08 18:17:11','Al');

SELECT * 
FROM @tbl


SELECT t.Id, t.[Name], x.MaxDate
FROM @tbl t
INNER JOIN (
    SELECT Id, MAX([Date]) AS MaxDate
    FROM @tbl
    GROUP BY Id
    ) x ON t.ID = x.ID 
        AND t.Date = x.MaxDate

CodePudding user response：

Try this query:

SELECT *
FROM tbl
WHERE (name, date) IN (
    SELECT tuple(name, max(date))
    FROM tbl
    WHERE name IN ('Al', 'Lily')
    GROUP BY name
)