SQL - How to remove duplicate rows by last entered value?

Question

I have this code at this link: How to remove duplicate lines?

;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 delete the first duplicate row entered, or should I have an additional date_of_entry column? I want to do this if I entered the same date_work and different hours PARTITION BY person_id, date_work to remove random duplicates.

If this is not possible, how can I remove duplicates with a higher watch?

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 either need to enter the date_of_entry field or another vector field, for example IDENTITY . For example, if the Id column is your INT IDENTITY , then your query would 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 no one changes the values ​​in the IDENTITY column

And if your conditions are suitable, then you can use the Hours column as your vector field in the grouping range person_id, date_work

And even the best way is to have UNIQUE INDEX over the columns person_id, date_work, hours , then there will be no way to add duplicates.

Answer 3

You can do this also with LINQ to SQL Remove duplicates in a 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 .