find duplicated rows from many columns
I have a query that takes few columns and I need to find if there are any duplicates rows, how would the where clause be:
Select col1,col2,col3
from myTable
where (all rows are the same)
result example:
col1 col2 col3
1 k10 101 John
2 k10 101 John
Select col1,col2,col3
from myTable
where (all rows are the same)
result example:
col1 col2 col3
1 k10 101 John
2 k10 101 John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do a basic select as below
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2,Col3, ORDER BY col1, col2,Col3) as RN, Col1, Col2 ,Col3
FROM TableName
Or you can also use Rank() Over instead of Row_Number() Function
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2,Col3, ORDER BY col1, col2,Col3) as RN, Col1, Col2 ,Col3
FROM TableName
Or you can also use Rank() Over instead of Row_Number() Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to make some modifications to make it work, but this query works finally :)
with CTE
as
(
Select col1,col2,col3 ,ROW_NUMBER()
OVER (PARTITION BY col1,col2,col3
ORDER BY col1,col2,col3) RN
from myTable
)
select RN,col1,col2,col3
from CTE
where RN > 1
Select A.ID, A.col1,A.col2,A.col3
from myTable A
inner join MyTable B on (A.col1=B.col1 and A.col2=B.col2 and A.col3=B.col3 and A.ID <> B.ID)