Select Distinct latest updated value in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

Dear All,

I have some datas in DB. I need to fetch datas by avoiding the duplicate records.

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.

UPDATE value is the latest modifications data.

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

Plesae help.

DB            
Sl No Action No Mob Email Status Date
1 ADD 123 1542 a@f.com 0 16/6/2019
2 ADD 1563 1425 b@f.com 0 16/6/2019
3 ADD 111 123 e@f.com 0 17/6/2019
4 ADD 222 1234 f@f.com 0 17/6/2019
5 UPDATE 222 12345 f@f.com 0 17/6/2019
6 ADD 333 12345 r@f.com 0 17/6/2019
7 UPDATE 333 1256 tt@f.com 0 17/6/2019
             
Output            
Action No Mob Email      
ADD 123 1542 a@f.com      
ADD 1563 1425 b@f.com      
ADD 111 123 e@f.com      
ADD 222 12345 f@f.com      
ADD 333 1256 tt@f.com      

Regards

Sreejith

Results 1 - 5 of 7 12
Posted 2 months ago

Hi meetsree83,

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


Posted 2 months ago

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.


Posted 2 months ago

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.


Posted 2 months ago

UPDATE value is the latest modifications data.


Posted 2 months ago Modified on 2 months ago

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

Action No Mob Email
ADD 123 1542 a@f.com
ADD 1563 1425 b@f.com
ADD 111 123 e@f.com
ADD 222 12345 f@f.com
ADD 333 1256 tt@f.com