Solved

removing a row with duplicate name by date

Posted on 2011-02-19
6
291 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

15 Experts available now in Live!

Get 1:1 Help Now