Removing a Duplicate Record
-
Hello All
I have a very old canned product that connects to a SQL database. It logs the user with the time that user logged in. This is for the license.
What it does not do is cleanup after itself. So once a user logs off, it does not always remove that user form the table. So there are multiple users in the table but with a different time stamp and a different ID.
What I would like to do is create an insert trigger that fires off to remove any duplicates before or after allowing a new user to log in.
The thing is, I do not, or will not know what the username will be. Here is an example of the data that is in the table
UserName, StartTime, UserID
Sally, 2008-03-24 10:09:13, 89
Sally, 2008-03-24 13:11:34, 107
John, 2008-03-24 09:23:45, 30
John, 2008-03-24 14:44:45 111
Steve, 2008-03-24 08:02:36, 24
(This is SQL Server, but I cannot space the columns apart on here, so i showed them as delimited)
What I would like to do is remove the oldest duplicates.
Which would be Sally with UserID 89, and John with UserID 30
If not a trigger, I can alway set this up to fire off the stored procedure every 30 minutes or so.
I am just not sure how to look for a duplicate, when I will not ever know the names of the users.
Greatly appreciate all your help
Thanks in advance
Andrew
-
You can remove duplicates using this
delete from mytablewhere exists (select * from mytable t2
where t2.UserName=mytable.UserName
and t2.StartTime>mytable.StartTime)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Are the UserNames unique? This appears to be the case in your example.
DECLARE @t TABLE (UserName VARCHAR(25), StartTime DATETIME, UserID INT)
INSERT @t
SELECT 'Sally', '2008-03-24 10:09:13', 89 UNION
SELECT 'Sally', '2008-03-24 13:11:34', 107 UNION
SELECT 'John', '2008-03-24 09:23:45', 30 UNION
SELECT 'John', '2008-03-24 14:44:45', 111 UNION
SELECT 'Steve', '2008-03-24 08:02:36', 24 UNION
SELECT 'Steve', '2008-03-25 08:02:36', 24 UNION
SELECT 'Mark', '2008-03-24 09:02:36', 24 UNION
SELECT 'Mark', '2008-03-24 08:22:36', 24
SELECT * FROM @t
-- replace the @t with your table's name and the code boelow will do the trick.
DELETE FROM @t
FROM @t AS a
INNER JOIN (SELECT
ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY StartTime DESC) AS rn
,UserName
,StartTime
,UserID
FROM
@t) AS b
ON a.StartTime = b.StartTime
AND a.UserID = b.UserID
AND a.UserName = b.UserName
WHERE b.rn <> 1
SELECT * FROM @t
FYI, this handles any case where there is more than 1 entry for any user, not just duplicates.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
It sounds like you need to create a stored procedure that will accept a username passed in. The stored procedure should delete the duplicate data for the user attempting to log on. Then you can create a DDL trigger that will execute the stored procedure. This in turn will cleanup your table and then login the user. This way the table will be self contained.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply