delete duplicate entries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete duplicate entries

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-03-08 : 10:28:39

hello,
I have a table that stores logon information.
sometimes the table doesn't correctly delete users logged on when they log out of the system.

how can I using T-SQL delete duplicate users from the table, but leave the newest one still in the table ?

so table is like :

username timein userid
jamie 07/03/2004 1
jamie 08/03/2004 1
john 08/03/2004 2

so in this table I would want to delete the first entry for jamie but keep the second.

any help will be great, thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-08 : 10:37:21

[code]create table test ( username varchar(10), timein datetime, userid int)
insert into test
select 'jamie','07/03/2004', 1 union all
select 'jamie','08/03/2004', 1 union all
select 'john','08/03/2004' ,2 union all
select 'john','08/04/2004' ,2

select * from test

delete test
from test t
left outer join
(
select userid,max(timein) timein from test
group by userid
) d on d.userid = t.userid and t.timein = d.timein
where d.userid is null and d.timein is null

select * from test

drop table test[/code]

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-08 : 10:38:04

CREATE TABLE #Logons(UserName VARCHAR(10), TimeIn DateTime, UserID INT)

INSERT INTO #Logons VALUES('DITCH', '2004-01-01 23:59:00', 1)
INSERT INTO #Logons VALUES('DITCH', '2004-01-02 23:59:00', 1)
INSERT INTO #Logons VALUES('X002548', '2004-02-01 23:59:00', 2)
INSERT INTO #Logons VALUES('X002548', '2004-01-01 23:59:00', 2)
INSERT INTO #Logons VALUES('NR', '2004-03-01 23:59:00', 3)

DELETE #Logons
FROM #Logons a
WHERE a.TimeIN <> (SELECT MAX(b.TimeIn) FROM #Logons b WHERE a.UserID = b.UserID)

select * from #logons

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-03-08 : 10:52:40

are these both the same ?

many thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-08 : 11:32:12

quote:
Originally posted by jamie

are these both the same ?

many thanks.

Did you cut and paste them in to QA to test them?

Those are some good posts in that EVERYTHING you need is there to see it work....

Only thing left out was the cleanup DROP from Ditch, SET NOCOUNT perhaps and a USE of a sample database...

Brett

8-)

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-03-09 : 04:48:12

Thanks Brett, I tested both and they seem to work.

Duane's SQL looks better for me, ( easier to read )

thanks again guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources