• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

removing a row with duplicate name by date

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
sykotex
Asked:
sykotex
  • 3
  • 2
2 Solutions
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
clinker83Commented:
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
Technology Partners: 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!

 
sykotexAuthor Commented:
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
 
clinker83Commented:
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
 
SharathData EngineerCommented:
Did you try my posts? Those should solve the problem.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now