How do i get the latest duplicate data from the table using sql server 2005

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