Finding rows with matching values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Finding rows with matching values

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-28 : 16:08:21

I have a table like this:

[WN][OS]
A....0
B....0
A....1
C....1
B....0
C....1

I want to select WN where WN+OS have duplicates. In other words, I would not select A because it's pair is A0, A1. But I would B and C because their pairs are B0, B0 and C1, C1.

SELECT WARRANTYNUMBER, NFOWNERSTATUS

FROM NFWARRANTYENDUSER

GROUP BY WARRANTYNUMBER, NFOWNERSTATUS

ORDER BY WARRANTYNUMBER

gets me the pairs, but I can't figure out how "loop" through...

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-28 : 16:28:20

create table #t1 (c1 char(1), c2 int)

insert into #t1 values('A',0)
insert into #t1 values('B',0)
insert into #t1 values('A',1)
insert into #t1 values('C',1)
insert into #t1 values('B',0)
insert into #t1 values('C',1)

SELECT t1.c1, t1.c2
from #t1 t1 join #t1 t2 ON t1.c1=t2.c1 AND t1.c2=t2.c2
GROUP BY t1.c1, t1.c2
HAVING COUNT(1) > 1

DROP Table #t1
GO

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 13:13:57

[code]
SELECT t1.*
from #t1 t1
where EXISTS (select 1 FROM #t1 WHERE c1=t1.c1 GROUP BY c1 HAVING COUNT(DISTINCT c2) =1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-08-30 : 03:00:18

select c1,c2
from (select c1,c2, row_number() over(partition by c1,c2 order by c1) rn from #t1) a
where a.rn >1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:27:29

quote:
Originally posted by bandi

select c1,c2
from (select c1,c2, row_number() over(partition by c1,c2 order by c1) rn from #t1) a
where a.rn >1

sorry not correct

this will give you duplicate instances of records with same c1,c2 values which is not what op want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources