Extracting the most recent record in a log table per server

Question

I have SQL server table with the data and structure below. I would like to write a SQL server query to extract the most recent value for every server.

The columns required will be all of the columns shown above, I have tried to use the max date but cannot get it to work, I think a ranking function is what is required here.

Answer 1

Try this:

DECLARE @T TABLE (  
	[SRN] int,  
	[Date] datetime,  
	[Server] varchar(20),  
	[Value] int  
);  
  
INSERT INTO  @T VALUES   
(1, '2022-08-10 10:00:00', 'Server1', 1),  
(2, '2022-08-10 09:59:00', 'Server1', 5),  
(3, '2022-08-01 12:00:00', 'Server2', 5),  
(4, '2022-08-10 10:00:00', 'Server2', 9),  
(5, '2022-07-10 23:00:00', 'Server3', 12),  
(1, '2022-02-10 14:00:00', 'Server4', 3);  
  
WITH CTE AS (  
	SELECT [SRN], [Date], [Server], [Value], ROW_NUMBER() OVER(PARTITION BY [Server] ORDER BY [Date] DESC) AS Ranked  
	FROM @T  
)  
  
SELECT [SRN], [Date], [Server], [Value]  
FROM CTE   
WHERE Ranked = 1;  

Output:

Answer 2

Try this.

WITH CTE (R,SRV,SRN,VL,DT) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY SERVER ORDER BY DATE), SERVER, SRN, VALUE FROM TABLENAME
)
SELECT * CTE WHERE R=1;