SQL - How can I remove duplicate rows by last entered value?

Question

I have this code from this link: How can I remove duplicate rows?

 ;WITH cte
 AS (SELECT ROW_NUMBER() OVER (PARTITION BY person_id, date_work, hours
                                   ORDER BY ( SELECT 0)) RN
     FROM   work_hours)
 DELETE FROM cte
 WHERE  RN > 1

Is it possible to remove first entered row of duplicate or i should have an extra column date_of_entry? I want to do this if i entered same date_work and different hours PARTITION BY person_id, date_work it remove randomly duplicates.

If it isn't possible, how can i remove duplicates with higher hours?

Answer 1

Add order by hours desc

;WITH cte
 AS (SELECT ROW_NUMBER() OVER (PARTITION BY person_id, date_work
                                   ORDER BY hours DESC) RN
     FROM   work_hours)
 DELETE FROM cte
 WHERE  RN > 1

Answer 2

Yes - you or have to introduce date_of_entry field or some other vector field like IDENTITY. For example if column Id is your INT IDENTITY, then your query will look like this:

 ;WITH cte
 AS (SELECT ROW_NUMBER() OVER (PARTITION BY person_id, date_work, hours
                                   ORDER BY ( SELECT Id DESC)) RN
     FROM   work_hours)
 DELETE FROM cte
 WHERE  RN > 1

Of course it is valid if nobody changes the values in IDENTITY column

And if your conditions suits - then you may want to use column Hours as your vector field within grouping range of person_id, date_work

And even better way is to have an UNIQUE INDEX over columns person_id, date_work, hours, then there will no any ability to add duplicates.

Answer 3

You could do it also with LINQ to SQL Remove duplicates in the list using linq , SQL with GROUP BY http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/ , and SQL DISTINCT http://www.databasedev.co.uk/eliminate_duplicates.html .