[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
5
Medium Priority
?
308 Views
Last Modified: 2012-05-11
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
Comment
Question by:gosi75
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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

by:
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

by:vdr1620
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

by:Sharath
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

Open in new window

0
 

Author Comment

by:gosi75
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

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question