How to get all rows from a table that have the latest date?

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

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
)

SELECT ID, name, Date
FROM
(
    SELECT
        *,
        rank() OVER (PARTITION BY name ORDER BY Date DESC) AS rn
    FROM t
    WHERE name IN ('Al', 'Lily')
)
WHERE rn = 1

┌──ID─┬─name─┬────────────────Date─┐
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
└─────┴──────┴─────────────────────┘

SELECT
    ID,
    name,
    Date
FROM
(
    SELECT
        name,
        arrayFilter((k, j) -> (j = 1), arrayReverseSort(i -> (i.2),
           groupArray((ID, Date))) AS arr, arrayEnumerateDense(arr)) AS a
    FROM t
    WHERE name IN ('Al', 'Lily')
    GROUP BY name
)
ARRAY JOIN
    a.1 AS ID,
    a.2 AS Date


┌──ID─┬─name─┬────────────────Date─┐
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 444 │ Al   │ 2022-09-08 18:17:11 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
│ 222 │ Lily │ 2022-08-30 17:47:16 │
└─────┴──────┴─────────────────────┘