0

Columns: ID (int) , Part (varchar), DF0 (varchar), DF1(varchar), DF2(varchar), DF3(varchar), DTI(DateTime)

All the samples I seem to find can get me the count or the data minus the ID and DTI. I need the ID and DTI in order to find, sort and delete the unnecessary rows. Can anyone help me out?

BillyDvd
  • 176
  • 7
  • Not related to your question, but SQL Server 2005 reached end of life over 5 years ago, you really should consider upgrading your version. With that being said, the method you would use in the latest version is the same in 2005. Use `ROW_NUMBER()` and partition by the columns you want to use to define a unique row, and order by the columns you want to use to define which row to keep. e.g. [How can I remove duplicate rows?](https://stackoverflow.com/a/3822833/1048425) – GarethD Jul 26 '21 at 16:03
  • Thanks! More progress than ever before! I have managed to get the query to return rows showing counts of many dups for that given match. However, I can not return the rows for all the matches. Is this possible? I have to validate the data prior to allowing the delete of the pseudo duplicates. And yes - the goal is to get the heck off 2005 server! It has been for a couple years, however it is our main system and due to 'covid' we ended up even more buried that previously so now it is just 'maintain' till we have our heads above water. Thanks again! – BillyDvd Jul 26 '21 at 17:09
  • In the linked answer if you replace `DELETE FROM cte WHERE RN > 1` with `SELECT * FROM cte WHERE RN > 1`, you will be able to preview the rows that are about to be deleted – GarethD Jul 26 '21 at 17:23
  • Reviewing the data with the select - Maybe it is only the row kept and count of how many total? RN – BillyDvd Jul 26 '21 at 17:54
  • If it is only the row to keep you want to select, then use `RN = 1`, Or if you just want counts, have no `WHERE` and then use `SELECT COUNT(*) AS Total, COUNT(CASE WHEN RN > 1 THEN 1 END) AS Delete, COUNT(CASE WHEN RN = 1 THEN 1 END) AS Keep FROM CTE` – GarethD Jul 27 '21 at 06:42
  • For the initial - I would like to see all the rows, including the one I am going to keep. All these rows will be inserted within a few 100 milliseconds of each other. The data 'one row' that I want to keep has to be kept for 10~15 years. So I am hesitant to delete without seeing ;) – BillyDvd Jul 27 '21 at 12:28

0 Answers0