How to delete by row number in SQL

Question

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

Answer 1

SELECT DISTINCT *
INTO #Temp
FROM dbo.industry

DELETE FROM dbo.industry

INSERT INTO dbo.industry
SELECT *
FROM #Temp

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

May be it is too late, but I am usually doing this

; with cte(rownum)as(
    select row_number () over(partition by [Col1], [Col2] order by Col3) from [table]
)
delete from cte where rownum > 1

Answer 6

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