Hi, I have done the query based on my understanding of ur question.
Let us have a look at the sample table
Declare @t table(Id int, Name Varchar(50))
Insert into @t
Select 1,'Name1' Union All
Select 2,'Name2' Union All
Select 1,'Name1' Union All
Select 1,'Name1' Union All
Select 3,'Name2' Union All
Select 2,'Name2' Union All
Select 1,'Name2' Union All
Select 4,'New Record'
I have 2 columns here ID and Name in which I will check the latest entry for duplicate Name field.
Algo
------
Step 1: Get all the duplicate records
Step 2: Order the records of the physical table in descending order such that their row order should not change.
For that first assign a rownumber for every row and then order them in descending order. This will guranteed to
give the latest record.
Step 3: Perform a inner join between step 1 and 2 record sets and get the top 1 record for obtaining the latest duplicate records
Query
-----
--Step1
;With AllDuplicateCTE As
(
Select Id,Name
From @t
Group by ID,Name
Having (Count(Name))> 1
)
--Step2
,AssignRowNumbersCTE As
(
Select *,Rn = Row_Number() over(order by (Select 1))
from @t
)
,ReorderRecordsFromBottom As
(
Select Top(Select Count(*) From AssignRowNumbersCTE) *
From AssignRowNumbersCTE
Order by rn desc
)
--Step 3
Select top 1 rb.Id,rb.Name
from ReorderRecordsFromBottom rb
join AllDuplicateCTE ad
on rb.Name = ad.Name
Order by rb.Rn desc
Output
------
For the given record, the output is
Id Name
1 Name2
Testcase 2:
Input
Id Name
1 Name1
2 Name2
1 Name1
1 Name1
3 Name2
2 Name2
1 Name2
4 New Record
5 Name1
Output
Id Name
5 Name1
Hope this helps
Best Regards,
Niladri Biswas
Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator