The objective is to demonstrate the capability of generating sequential number using Row_Number() function inside a group and to perform Update operation.
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
Let us first make the initial setup as under
--Create the table
CREATE TABLE #t
ID INT IDENTITY(1,1),
--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
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(
Rn=Row_Number() OVER(PARTITION BY GroupID ORDER BY EntryTime DESC)
--Project the reocrds
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
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
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.