title: Delete all but top record in SQL server
body: <p>I'm trying to write a script to delete all records except for the top one (if it's just any one that's fine too, there just have to be one record left). Here's the format I have as of now:</p>
  
  <pre><code>DELETE FROM table
  WHERE id NOT IN (
    SELECT id
    FROM (
      SELECT id
      FROM table
      ORDER BY id DESC
      LIMIT 1 ))
  </code></pre>
  
  <p>But I realize that LIMIT is not a recognized function in SQL server 2005, so I'm kind of stuck as to what to do. Anyone have any expert knowledge on this topic? And these records are NOT duplicated, I just want to take out all but one record.</p>
  
  <p>UPDATE:</p>
  
  <p>I realized that what I'm trying to accomplish here isn't completely clear. What I need to do is to delete all but the top record in this table given that it has the same value in another column (let's call it 'anotherid').</p>
  
  <p>so its like from this:</p>
  
  <pre><code>id        value       anotherid       
  1          3           1
  2          4           1
  3          5           2
  4          6           2
  5          7           2
  </code></pre>
  
  <p>To this:</p>
  
  <pre><code>id        value       anotherid       
  1          3           1
  3          5           2
  </code></pre>