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

sofia
 
on Jun 07, 2021 05:22 AM
594 Views

hi dharmander sir,

i have two database table,

1st table is (ipaddress, name) 2nd table is (ipaddress,pingtime,ipstatus)

there is contineously added record in 2nd tablle after every 15 minutes against every ipaddress.

i want to display ipaddress, name , pingtime ,ipstatus of every ipaddress that shown its latest record which is latest  added in 2nd database table.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 07, 2021 08:21 AM

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

ipaddress pingtime status name
161.738.5.20 6 june 2021 4:00PM 100 hall
161.738.56.267 7 june 2021 3:20PM 100 room