CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
user3482471
user3482471

Reputation: 227

Same data, different results when using PARTITION BY and ROW_NUMBER

I have been attempting to write a script to find duplicate records. However this will require one of the fields to be the same and the other one to be different. I am using the below 2 lines in my SELECT. 

ROW_NUMBER () OVER (PARTITION BY col_1 ORDER BY col_2) AS 'ROWNUMBER',
ROW_NUMBER () OVER (PARTITION BY col_2 ORDER BY col_1) AS 'ROWNUMBER2',

Once this has been used I then select my results from a TEMP table where both columns are > 1. Now this has produced my results to be correct in one environment however when running the same script in another environment (backup from the weekend) my results are different.

Can some explain to me why this could happen?

Many Thanks in advance.

Upvotes: 0

Views: 1246

Answers (1)

sagi
sagi

Reputation: 40481

Why are you using row_number? This is not necessary at all, you should use group by:

SELECT col_1,col_2 from YourTable
group by col_1,col_2 having count(*) > 1

this query will return all duplicated rows

Edit: If you have a 3rd column which you are deciding who is a dup according to it, you should do:

SELECT col_3 from yourTable
group by col_3 from yourTable having count(*) > 1

Upvotes: 1

Related Questions