[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# find duplicated rows from many columns

Posted on 2011-05-11
Medium Priority
308 Views
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
0
Question by:gosi75

LVL 32

Expert Comment

ID: 35738249

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)
0

LVL 32

Accepted Solution

Ephraim Wangoya earned 1000 total points
ID: 35738288
or use a CTE

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 MyTable
0

LVL 16

Expert Comment

ID: 35738353
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
0

LVL 41

Assisted Solution

Sharath earned 1000 total points
ID: 35744159
In ewangoya's query, to find duplicates, you need to have the WHERE clause on RN.
``````;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 MyTable
where RN > 1
``````
0

Author Comment

ID: 35745156
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
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month18 days, 14 hours left to enroll