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

Posted by Mahendrabasutkar under Sql Server on 9/6/2011 | Points: 10 | Views : 1213 | Status : [Member] | Replies : 1
Hi All,

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

Thanks&Regards
mahendrabasutkar




Responses

Posted by: Niladri.biswas on: 9/6/2011 [Member] Platinum | Points: 25

Up
0
Down
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

Login to post response