How to delete by row number in SQL

user_4578075 0
Viewed 52551

I need to delete from row number 475 to 948 due to them being duplicates of rows 1-474. It would be something close to this, I presume, or is there more to it?

DELETE FROM dbo.industry WHERE row_number between 475 and 948
sql-delete ssms
7 Answers
DELETE FROM dbo.industry
WHERE COLUMN_NAME IN      -- Choose a column name
  (SELECT TOP 1000
     COLUMN_NAME,          -- Choose a column name
     ROW_NUMBER() OVER( ORDER by COLUMN_NAME ASC) AS Row_Number
   FROM dbo.industry
   WHERE Row_Number BETWEEN 475 AND 948 )

COLUMN_NAME can be any column name of your table u want.

user_84594030

If you are trying to delete using the Row_Number function, and you get an error of

Windowed functions can only appear in the SELECT or ORDER BY clauses

you can revise the SQL to have it in the select clause as in the example below:

Delete T 
From (Select Row_Number() Over(Partition By [IndustryType], [IndustryDescription] order By [ID]) As RowNumber,* 
From dbo.industry) T
Where T.RowNumber > 1
user_60726480 
ALTER TABLE dbo.industryADD COLUMN tmpRowNumber COUNTER
DELETE * FROM dbo.industry WHERE tmpRowNumber IS BETWEEN 475 AND 948
ALTER TABLE dbo.industry DROP COLUMN tmpRowNumber

Carefull though, your DBS may not consider row #1 as row #1 as tables are unsorted

user_87525440 
SELECT DISTINCT *
INTO #Temp
FROM dbo.industry

DELETE FROM dbo.industry

INSERT INTO dbo.industry
SELECT *
FROM #Temp
user_10839230 
DELETE FROM dbo.industry WHERE dbo.industry.
REPLACE WITH PK COLUMN NAME| IN (SELECT TOP 948 dbo.industry
REPLACE WITH  PK COLUMN NAME| FROM dbo.industry WHERE dbo.industry
REPLACE WITH  PK COLUMN NAME| > 475 ORDER BY dbo.industry 
REPLACE WITH  PK COLUMN NAME|)
user_70591930

This is not really an answer. There were a few issues with the data that made the answers above (while excellent) unrelated. I simply deleted the table and then re-imported it from fixed width. This time, I was more careful and did not have the duplication.

user_45780750
Related