Reputation: 2285
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
Upvotes: 3
Views: 5692
Reputation: 280262
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.
Upvotes: 4
Reputation: 1764
I assume there's some kind of unique identifier on your table?
for that do something like this
delete from [table]
where [uniqueColumn] <> 'value';
If you don't have unique columns on.
manually go into the table,
change a value on the row you want to be something unique like "OMGWTFBBQSauce".
Then do
delete from [table]
where [column] <> 'OMGWTFBBQSauce';
Upvotes: 1
Reputation: 58615
delete from table where id <> (select top 1 id from table order by id desc)
Upvotes: 9