Solved

removing a row with duplicate name by date

Posted on 2011-02-19
6
282 Views
Last Modified: 2012-08-13
Hi
I have a SQL table with various columns, in this table I have a username column and a datetime column, I would like to delete the rows which contain duplicate usernames but only the duplicates with the oldest datetime value
0
Comment
Question by:sykotex
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34935754
try this.
DELETE a 
  FROM your_table a 
 WHERE datetime_col NOT IN (SELECT MAX(datetime_col) 
                              FROM your_table b 
                             WHERE a.username = b.username)

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 34935757
or this.
DELETE FROM your_table a 
      WHERE EXISTS (SELECT 1 
                      FROM your_table b 
                     WHERE a.username = b.username 
                           AND b.datetime_col < a.datetime_col)

Open in new window

0
 
LVL 3

Assisted Solution

by:clinker83
clinker83 earned 250 total points
ID: 34937127
Borrowing some ideas from Sharath, try the following

DELETE FROM your_table as A
WHERE
COUNT(username) > 1
AND
datetime NOT IN (SELECT MAX(datetime) FROM your_table as B WHERE A.username = B.username)
GROUP BY username

Obviously backup your database before running delete and update statements. Also run statements as select to see if the data you expect to be deleted is returned.

If the above doesn't work you may need to look at creating temp table with all usenames that have count(username) >1 on original table. From there you can delete everything apart from username that satisfys MAX(datetime) grouping by username

Best of Luck!
0
Highfive Gives IT Their Time Back

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!

 
LVL 1

Author Comment

by:sykotex
ID: 34941790
Hi
I dont think I was clear
This is the table below

id         Username                 Date_time
1          Andy                        2/21/2011 12:15:14
2          Andy                        2/21/2011 12:45:16
3          James                      2/21/2011 12:30:45


I would like the job to remove the earliest Andy as this is a duplicate name with the earliest time (which would be id 1 in this case)

Thanks

0
 
LVL 3

Expert Comment

by:clinker83
ID: 34943410
In your example table above

SELECT * FROM your_table as A
WHERE
COUNT(username) > 1
GROUP BY Username

This will return
id         Username                 Date_time
1          Andy                        2/21/2011 12:15:14
2          Andy                        2/21/2011 12:45:16

The statement in my original post :
DELETE FROM your_table as A
WHERE
COUNT(username) > 1
AND
datetime NOT IN (SELECT MAX(datetime) FROM your_table as B WHERE A.username = B.username)
GROUP BY username

Should bring back everything apart from the latest row i.e it will return
1          Andy                        2/21/2011 12:15:14

Therefore when using the DELETE statement that I first posted it should delete every record that has a duplicate apart from the latest one, which is what you want to do.

I'm just looking at this from a logical perspective. Maybe the syntax of the statement is not correct. Have you tried it?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34943969
Did you try my posts? Those should solve the problem.
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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

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

20 Experts available now in Live!

Get 1:1 Help Now