Solved

Delete duplicate rows

Posted on 2008-10-08
2
161 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:chrisryhal
2 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now