CODEWITHSUNDEEP
sqlsql-serversql-delete
Euthyphro
Euthyphro

Reputation: 710

Deleting all but top 10 rows

I have the following SQL statement which seems to be deleting every row in the selected table. What it should be doing is deleting all but the top 10 where the difficulty level equals one.

DELETE FROM Scores
WHERE Highscore_ID 
NOT IN (SELECT TOP 10 highScore FROM Scores WHERE difficulty = 1 ORDER BY HighScore DESC)

Any suggestions as to why this would be deleting all rows? When running the subquery, it selects the proper rows, however, when deleting, it seems to want to delete every row.

Upvotes: 0

Views: 7056

Answers (4)

paulsm4
paulsm4

Reputation: 121649

My first guess would be "SELECT TOP 10 highScore FROM Scores WHERE difficulty = 1 ORDER BY HighScore DESC" could be returning null.

My second guess would be "highscore_id" is different from "highscore", so there's no overlap (and nothing's deleted).

Definitely double-check your subquery, and make sure it's returning the keys you expect!

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

Try this:

with a as
(
select ROW_NUMBER() over(order by name) as ordinal, * from test
)
delete from a where a.ordinal > 10;

Related: http://www.ienablemuch.com/2012/03/possible-in-sql-server-deleting-any-row.html

Sample data:

CREATE TABLE [beatles]
    ([name] varchar(14));

INSERT INTO [beatles]
    ([name])
VALUES
    ('john'),
    ('paul'),
    ('george'),
    ('ringo'),
    ('pete'),
    ('brian'),
    ('george martin');

Query:

with a as
(
  select *, row_number() over(order by name) ordinal
  from beatles
)
delete from a
where ordinal > 4;

select * from beatles;

Prior deleting:

NAME
brian
george
george martin
john
paul
pete
ringo

After deleting:

NAME
brian
george
george martin
john

Live test: http://www.sqlfiddle.com/#!3/0adcf/6

Upvotes: 1

YvesR
YvesR

Reputation: 6222

You compare Hichscore_Id with a column highScore. Does those columns really have the same values?

Then it should be 

DELETE FROM Scores 
WHERE Highscore_ID NOT IN
          (SELECT TOP 10 HighScore_ID 
           FROM Scores 
           WHERE difficulty = 1 
           ORDER BY HighScore DESC);

EDIT:

Try this

DELETE FROM Scores
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY highscore) AS your_row, * 
      FROM Scores 
      WHERE difficulty = 1 
      ORDER BY HighScore DESC) AS score2 ON score2.HighScore_ID = Scores.HighScore_ID
WHERE Scores.difficulty = 1 AND score2.your_row>10

Upvotes: 4

David Brabant
David Brabant

Reputation: 43499

Don't know if this is a typo or a real error but your select clause should refer to highscore_id, not highscore.

Upvotes: 2

Related Questions