title: How to Delete Duplicate Rows in SQL 2000?
body: <p>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.</p>
  
  <pre><code>select scorestudentid, scoreadvisor, scorecorrect, count(*) 
  from scores
  where scoretestid = 3284
  group by scorestudentid, scoreadvisor, scorecorrect
  having count(scorestudentid) &gt; 1
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So I put this together thinking it would work:</p>
  
  <pre><code>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) &gt; 1)
  </code></pre>
  
  <p>It really seems like it should be a simple concept but I'm not getting it. </p>
  
  <p>Based on Thomas script I updated the query to fit but it still doesn't work. </p>
  
  <pre><code>Delete Scores
  Where Exists    (
                  Select 1
                  From Scores As S2
                  Where S2.ScoreStudentId = Scores.ScoreStudentId
                          And S2.ScoreAdvisor = Scores.ScoreAdvisor
                          And S2.ScoreCorrect = Scores.ScoreCorrect
                  Group By S2.ScoreStudentId, S2.ScoreAdvisor, S2.ScoreCorrect
                  Having Count(*) &gt; 1
                      And Min(S2.NewScoreID) = Scores.NewScoreID
                  )
      And Scores.ScoreTestId = 3284
  </code></pre>