?
Solved

removing a row with duplicate name by date

Posted on 2011-02-19
6
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

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 41

Accepted Solution

by:
Sharath earned 1000 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 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 41

Expert Comment

by:Sharath
ID: 34943969
Did you try my posts? Those should solve the problem.
0

Featured Post

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

Question has a verified solution.

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

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.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month12 days, 10 hours left to enroll

777 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