Finding a recent most duplicate records from SQL Server 2012

Finding a recent most duplicate records from SQL Server 2012

I want to find the recent duplicate records from SQL Server 2012. Here is the table structure I have.

I have table name called UserRegistration which contains the duplicate of UserID(GUID) and in same table, I have CreatedDate Column as well (Date). Now I want to find the recent duplicate records from this table.

Here is the same data.

id  FirstName  LastName  CreatedDate  UserID  109 FirstNameA  LastNameA 28-04-2015  GUID1  110 FirstNameC  LastNameD 19-05-2015  GUID2  111 FirstNameE  LastNameF 22-05-2015  GUID1  

If you notice on above tables, GUID 1 are having the duplicate, Now I want to find the recent one means it should return me only those rows with duplication but recent data. So in above table structure, it should return me 111 because record has been created recently compared to the 109. I believe you understand.

Do let me know if you have any question. I am happy to answer. Thanks. Awaiting for the reply.

Harshal

Answer by Abhishek for Finding a recent most duplicate records from SQL Server 2012

Try the below query this should do the work based on your i/p data -

create table #UserRegistration (id  int,FirstName  varchar(20),LastName  varchar(20),CreatedDate  date,UserID varchar(20))  insert into #UserRegistration  select 109, 'FirstNameA',  'LastNameA', '2015-04-28',  'GUID1' union  select 110, 'FirstNameC',  'LastNameD', '2015-05-19',  'GUID2' union  select 111, 'FirstNameE',  'LastNameF', '2015-05-22',  'GUID1'  select id,  FirstName,  LastName,  CreatedDate,  UserID from  (SELECT ur.*,row_number() over(partition by UserID order by CreatedDate) rn  FROM #UserRegistration ur) A  where rn > 1  

Answer by Ivan Sivak for Finding a recent most duplicate records from SQL Server 2012

You could use CTE. Group your records by UserID and give your particular row a rank ordered by CreatedDate.

insert into tab(id, FirstName, LastName, CreatedDate, UserID)   values(109, 'FirstNameA', 'LastNameA', '2015-04-28', 'guid1'),        (110, 'FirstNameC', 'LastNameD', '2015-05-19', 'guid2'),        (111, 'FirstNameE', 'LastNameF', '2015-05-22', 'guid1');    with cte as  (    select id, ROW_NUMBER() over (partition by UserID order by CreatedDate asc) as [Rank],           FirstName, LastName, CreatedDate, UserID    from tab  )  select id, FirstName, LastName, CreatedDate, UserID from cte where Rank > 1  

Rank > 1 condition is responsible for retrieving duplicated items.

sqlfiddle link: http://sqlfiddle.com/#!6/4d1f2/6

Answer by tobypls for Finding a recent most duplicate records from SQL Server 2012

Solved this by using tmp-tables:

SELECT  a.UserID,          MAX(a.CreatedDate) As CreatedDate  INTO #latest   FROM  a  GROUP BY a.UserID  HAVING COUNT(a.UserID) > 1    SELECT b.id  FROM #latest a  INNER JOIN  b ON a.UserID = b.UserID AND a.CreatedDate = b.CreatedDate  

Answer by Thorsten Kettner for Finding a recent most duplicate records from SQL Server 2012

You'd rank your records with ROW_NUMBER() to give all last records per userid #1. With COUNT() you make sure only to get the userids having more than one record.

select    id, firstname, lastname, createddate, userid  from  (    select      id, firstname, lastname, createddate, userid,      row_number() over (partition by userid oder by createddate desc) as rn,      count(*) over (partition by userid) as cnt    from userregistration   ) ranked  where rn = 1 -- only last one  and cnt > 1; -- but only if there is more than one record for the userid  

This gets the latest record for every userid that has duplicates.

Answer by vipin for Finding a recent most duplicate records from SQL Server 2012

try this,

SELECT * FROM TableName tt WHERE   exists(select MAX(createdDate)   from TableName  where tt.UserID = UserID   group by UserID   having MAX(createdDate)= tt.createdDate)  

I think your createddate field is not a date field, then try Format

Answer by Sagar Shirke for Finding a recent most duplicate records from SQL Server 2012

    WITH TempAns (id,UserID,duplicateRecordCount)      AS      (          SELECT id,          UserID,           ROW_NUMBER()OVER(partition by UserID ORDER BY id)           AS duplicateRecordCount          FROM #t      )      select * from #t where id in (             select max(id )             from TempAns             where duplicateRecordCount > 1             group by name )  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72