How to Delete Duplicate Rows in SQL 2000?

Posted by Mikecancook on Stack Overflow See other posts from Stack Overflow or by Mikecancook
Published on 2010-04-21T21:36:08Z Indexed on 2010/04/21 21:43 UTC
Read the original article Hit count: 158

Filed under:
|

I thought I had this figured out but it turns out I'm just deleting the first record. The following returns the duplicate rows. All have a count of 2. I just want to delete the first one for each duplicate record.

select scorestudentid, scoreadvisor, scorecorrect, count(*) 
from scores
where scoretestid = 3284
group by scorestudentid, scoreadvisor, scorecorrect
having count(scorestudentid) > 1

Which returns:

scorestudentid  scoreadvisor  scorecorrect  no column name
13033719        28059     3.0           2
13033777        28086     3.0           2
13033826        28147     3.0           2
13033960        28023     3.0           2

So I put this together thinking it would work:

set rowcount 1
delete
from scores
where scoretestid = 3284 
and scorestudentid in (
    select scorestudentid
    from scores
    where scoretestid = 3284
    group by scorestudentid
    having count(scorestudentid) > 1)

It really seems like it should be a simple concept but I'm not getting it.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2000