Delete all but top record in SQL server

Question

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:

DELETE FROM table
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM table
    ORDER BY id DESC
    LIMIT 1 ))

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.

UPDATE:

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').

so its like from this:

id        value       anotherid       
1          3           1
2          4           1
3          5           2
4          6           2
5          7           2

To this:

id        value       anotherid       
1          3           1
3          5           2

Answer 1

delete from table where id <> (select top 1 id from table order by id desc)

Answer 2

Given the update, as I understand it:

DECLARE @x TABLE(id INT, value INT, anotherid INT);

INSERT @x SELECT 1, 3, 1
UNION ALL SELECT 2, 4, 1
UNION ALL SELECT 3, 5, 2
UNION ALL SELECT 4, 6, 2
UNION ALL SELECT 5, 7, 2;

;WITH y AS 
(
  SELECT id, rn = ROW_NUMBER() OVER 
   (PARTITION BY anotherid ORDER BY id) -- or order by value?
   FROM @x -- replace with your dbo.tablename
)
DELETE y WHERE rn > 1;

SELECT id, value, anotherid FROM @x;

Results:

id  value  anotherid
--- ------ ---------
1   3      1
3   5      2

---

Answer to original question

A couple of other options:

-- use a CTE to identify the highest id,
-- then delete all rows except that one
;WITH x AS 
(
  SELECT id, rn = ROW_NUMBER() OVER (ORDER BY id DESC) 
  FROM dbo.table
)
DELETE x WHERE rn > 1;

-- use a subquery to get the max, and delete
-- all id values lower than that result
DELETE dbo.table WHERE id < (SELECT MAX(id) FROM dbo.table);

Note that these will behave differently if id is not unique. Let's say the highest id is 10, and there are three rows with id = 10. The top answer will leave one row in the table; the bottom answer (and Adrian's answer) will leave three rows. If this is your scenario and your intention is to leave exactly one row in the table even in the event of a tie, you can add additional tie-breaking criteria to the ORDER BY in the top query.