Delete duplicate rows

Sometimes with this device we have, its possible that duplicate column data will be placed within the SQL Server table.  So, we are trying to create a SPROC in which will remove these DUPLICATES but still obviously preserve the data.   Basicaly would like the query to be based on the ENTRY_DATE column.  

Basically, if there are ANY two entry dates of the same, preserve ONE row and wipe the rest.  There will NEVER be TWO entries within the same second, its just not possible.  

Example would be

          FIRSTNAME           LASTNAME                  ENTRY_DATE
1            Chris                        Jones                     10/7/2008 4:50:00 PM
2            Chris                        Jones                     10/7/2008 4:50:00 PM
3            Chris                        Jones                     10/7/2008 4:50:00 PM

So in the this example, rows 2 and 3 will be deleted, but row 1 will stay because we still want the data.
LVL 2
chrisryhalAsked:
Who is Participating?
 
sm394Commented:
SELECT DISTINCT FIRSTNAME, LASTNAME,ENTRY_DATE
 into tmp FROM TableName

delete from TableName

insert into TableName(FIRSTNAME, LASTNAME,ENTRY_DATE)
select FIRSTNAME, LASTNAME,ENTRY_DATE from tmp2

drop table tmp2
0
 
chapmandewCommented:
with mycte(ranking, firstname, lastname, entrydate)
as
(
select ranking = dense_rank() over(partition by firstname, lastname, entry_date order by newid()
, firstname, lastname, entry_date
from yourtable
)

delete from mycte
where ranking > 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.