Update record based on the latest time & Row_Number in SQL Server

Introduction

Suppose we have a table as under

The objective is to update the status of only those records in the group which has the latest EntryTime. In this article we will look into the TSQL script for doing so

Initial Setup

Let us first make the initial setup as under

--Create the table
CREATE TABLE #t
(
	ID INT IDENTITY(1,1),
	GroupID int,
	Status int,
	EntryTime datetime
)

--Insert some rows to it
INSERT INTO #t VALUES(1001,1,'2016-10-07 15:05:02.100')
INSERT INTO #t VALUES(1001,1,'2016-10-07 15:05:04.577')
INSERT INTO #t VALUES(1001,1,'2016-10-07 15:05:05.790')

INSERT INTO #t VALUES(1002,1,'2016-10-07 15:05:02.103')
INSERT INTO #t VALUES(1002,1,'2016-10-07 15:05:03.580')
INSERT INTO #t VALUES(1002,1,'2016-10-07 15:05:05.793')

--Project the records
Select *
From #t

--Clean Up
DROP TABLE #t

Straight to program

Let us first sort the records in descending order inside the group based on the EntryTime as under

;WITH CTE AS(
	SELECT
		Rn=Row_Number() OVER(PARTITION BY GroupID ORDER BY EntryTime DESC)
		,*
	FROM #t)

--Project the reocrds
SELECT *
FROM CTE

The Row_Number() function will generate the sequential numbers but since the Partition By clause is present, so the sequential numbers will be generated inside the Groups. The presence of ORDER BY EntryTime DESC guarantees that the records will be sorted in descending order based on the EntryTime.

Now we need to execute the update statement as under

--The update statement
UPDATE t
SET t.Status = 0
FROM #t t
INNER JOIN CTE c 
ON t.id = c.id
WHERE c.Rn=1 --Filter by Latest time

We are performing an update statement by joining the original table and CTE which is filtered by latest EntryTime.

The result is as under

Conclusion

In this article we have learnt the use of Row_Number() function to generate sequential numbers inside a group and to perform update by performing Join. Hope this will be helpful. Thanks for reading. Zipped file attached.