Select max record from each group in DateTime column in SQL Server

Hi sofia,

Refer below query.

SQL

DECLARE @Table1 AS TABLE(ipaddress VARCHAR(20),name VARCHAR(20))
INSERT INTO @Table1 VALUES('161.738.56.267','room')
INSERT INTO @Table1 VALUES('161.738.5.20','hall')
INSERT INTO @Table1 VALUES('161.738.4.128','gate')
INSERT INTO @Table1 VALUES('161.738.436.627','coredoor')

DECLARE @Table2 AS TABLE(ipaddress VARCHAR(20),pingtime VARCHAR(20),status int)
INSERT INTO @Table2 VALUES('161.738.56.267','6 june 2021 3:01AM',100)
INSERT INTO @Table2 VALUES('161.738.56.267','6 june 2021 4:00AM',100)
INSERT INTO @Table2 VALUES('161.738.56.267','6 june 2021 5:00PM',50)
INSERT INTO @Table2 VALUES('161.738.56.267','7 june 2021 3:16AM',75)
INSERT INTO @Table2 VALUES('161.738.56.267','7 june 2021 3:20PM',100)
INSERT INTO @Table2 VALUES('161.738.56.267','7 june 2021 5:00AM',100)
INSERT INTO @Table2 VALUES('161.738.5.20','6 june 2021 3:10AM',100)
INSERT INTO @Table2 VALUES('161.738.5.20','6 june 2021 4:00PM',100)
INSERT INTO @Table2 VALUES('161.738.5.20','6 june 2021 5:30AM',100)

SELECT t.ipaddress,t.pingtime,t.status,t1.name
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY (ipaddress) ORDER BY CONVERT(DATETIME,[pingtime],103) DESC) Row_No,*
    FROM @Table2
    GROUP BY ipaddress,pingtime,status
)t
INNER JOIN @Table1 t1 ON t.ipaddress = t1.ipaddress
WHERE Row_No = 1

Output