Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Delete duplicate rows

Posted on 2008-10-08
2
Medium Priority
?
209 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
ID: 22670742
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 2000 total points
ID: 22672035
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

569 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