CODEWITHSUNDEEP
sql-server
Rueful Rabbit
Rueful Rabbit

Reputation: 121

Remove all but last entry by column

I would like to select distinct NAME and delete rows where the ID <> Max(ID) for that NAME.

The table is as follows ...

NAME                    DATE                    ID
BudgetX.dbo.Persons 2015-02-13 13:53:38.780 1
BudgetX.dbo.Persons 2015-02-13 13:53:38.780 2
BudgetX.dbo.ILFS        2015-02-13 14:29:55.347 3
BudgetX.dbo.ILFS        2015-02-13 14:29:55.347 4
BudgetX.dbo.ILFS        2015-02-13 14:30:10.813 5

I would like to remove all but rows 2 and 5.

Any ideas appreciated.

Thanks, RR

Upvotes: 0

Views: 32

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

You could use a CTE + ROW_NUMBER:

WITH CTE AS
(
   SELECT Name, Date, ID,
          RN = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID DESC)
   FROM dbo.TableName
)
DELETE FROM CTE WHERE RN > 1

Upvotes: 3

Related Questions