CODEWITHSUNDEEP
sqlsql-server-2008
Anton Belev
Anton Belev

Reputation: 13523

SQL: Find records with matching values on a set of columns

I'm using SQL Server 2008 RD.

I've got the following table say myTable consisting of a number of columns. AccID and AccName are the columns in which I'm interested in and non of them is a primary-key. So I want to obtain all the records having at least a duplicate (there could be more than 2 rows agreeing on AccID and AccName).

  AccID     AccName
1 333       SomeName1
2 333       SomeName1
3 444       SomeName2
4 444       SomeName2
5 444       SomeName2

How can I do this with SQL?

Upvotes: 2

Views: 6954

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you want all the records (not just the names of the duplicate values), I would suggest using the count() window function:

select t.*
from (select t.*, count(*) over (partition by AccID, AccName) as cnt
      from mytable t
     ) t
where cnt > 1;

Upvotes: 2

Ian Preston
Ian Preston

Reputation: 39566

select t.*
from myTable t
inner join
(
  select AccID, AccName
  from myTable
  group by AccID, AccName
  having count(1) > 1
) agg on t.AccID = agg.AccID and t.AccName = agg.AccName

Upvotes: 1

Robert
Robert

Reputation: 25753

Try this way:

select m1.AccID, m1.AccName 
from   myTable m1
join   ( select AccID,AccName 
         from myTable  
         group by AccID,AccName
         having count(1) = 2 
        ) m2 on   m1.AccID   = m2.AccID
             and  m1.AccName =  m2.AccName

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Use GROUP BY clause and COUNT aggregate function with condition specified by 

HAVING COUNT(*) > 1

Upvotes: 3

Related Questions