0

I have a table Income where I want to delete all, but one, rows that have same value in the column "Date", say 2016-05-08. Can someone help? TIA.

Aditya Singh
  • 1
  • 3
  • 4
    Possible duplicate of [How to delete duplicate rows in sql server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) – Eric Citaire Jul 03 '17 at 11:34
  • 1
    How would you pick that one row? In any case, the duplicate question shows the various ways to identify and delete duplicates. – Panagiotis Kanavos Jul 03 '17 at 11:36

1 Answers1

0

I would do this using row number:

with todelete as (
      select i.*, row_number() over (partition by date order by date) as seqnum
      from income
     )
delete todelete
    from todelete
    where seqnum > 1;

You can control which row is kept by adjusting the order by clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786