Remove Duplicate Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove Duplicate Rows

Author

Topic

Showkat
Starting Member

1 Post

Posted - 2011-04-30 : 01:48:05

Dear,

I searched a lot regarding my issue, At last i came here to find the answer of my problem. First please go through below sample table.

ATTRecord
Emp_Code | Name | Date
1 | ABC | 4/30/2011 8:00
2 | XYZ | 4/30/2011 9:00
3 | HXE | 4/30/2011 6:00
1 | ABC | 4/30/2011 7:00
1 | ABC | 4/29/2011 4:00
2 | XYZ | 4/30/2011 3:00
1 | ABC | 4/30/2011 2:00

If you notice Emp_Code 1 has duplicate entries on both dates. What i want is SQL statement or Procedure to remove duplicate Entires but keeping One entry live in table on each day with lowest time.
Which means for Emp_Code 1 below records should get deleted
1 | ABC | 4/30/2011 8:00
1 | ABC | 4/29/2011 4:00

I hope you do understand my problem and will help me to sort this out.
Thanx for your Time, Attention and Support.

With Regards

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-30 : 08:15:15

From your example, it seems like you want to keep the highest (latest) time rather than the lowest (earliest) time. The code below will pick the latest time.

select Emp_Code,Name,Date from
(
select
   *,
   row_number() over (partition by Emp_Code, dateadd(dd,datediff(dd,0,Date),0) order by Date desc) as rn
from
   Employees
) s where rn = 1

This will work only if you are on SQL 2005 or later.

If you want to pick the earliest time, change the "order by Date desc" to "order by Date asc".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources