Select Distinct latest updated value in SQL Server

Hi meetsree83,

Will you let us know what is condtion means on which basis condition you are looking this output ?

Based on the NO we will get duplicate records.If 2 records come for same NO,then we need one record with latest modifications implemented on the rows.

For example:If MOb NO chnaged the the new mob no will be fetch and gave the o/p as one record.

Based on your record there are duplicate rows with only Date. So how can we identify the last updated record.

You need to save Date with Time inorder to identify the last updated record.

UPDATE value is the latest modifications data.

Hi meetsree83,

Refer below sample query.

SQL

DECLARE @TestTable AS TABLE(SiNo INT, [Action] VARCHAR(20),[No] INT, Mob INT, Email VARCHAR(50), [Status] INT, [Date] DATETIME)
INSERT INTO @TestTable VALUES(1,'ADD',123,1542,'a@f.com',0,'2019/6/16')
INSERT INTO @TestTable VALUES(2,'ADD',1563,1425,'b@f.com',0,'2019/6/16')
INSERT INTO @TestTable VALUES(3,'ADD',111,123,'e@f.com',0,'2019/6/17')
INSERT INTO @TestTable VALUES(4,'ADD',222,1234,'f@f.com',0,'2019/6/17')
INSERT INTO @TestTable VALUES(5,'UPDATE',222,12345,'f@f.com',0,'2019/6/17')
INSERT INTO @TestTable VALUES(6,'ADD',333,12345,'r@f.com',0,'2019/6/17')
INSERT INTO @TestTable VALUES(7,'UPDATE',333,1256,'tt@f.com',0,'2019/6/17')

SELECT 'ADD'[Action],[No],Mob,Email FROM (
Select *,ROW_NUMBER() OVER (PARTITION BY [No] ORDER BY [Action] DESC)rowNumber  
FROM @TestTable )t
WHERE t.rowNumber = 1
ORDER BY SiNo

Output