| Author |
 Topic  |
|
|
Spica66
Starting Member
23 Posts |
 Posted - 08/28/2012 : 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...
|
Edited by - Spica66 on 08/28/2012 16:13:27
|
|
|
chadmat
The Chadinator
USA
1957 Posts |
Posted - 08/28/2012 : 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
India
47099 Posts |
Posted - 08/29/2012 : 13:13:57
|
SELECT t1.*
from #t1 t1
where EXISTS (select 1 FROM #t1 WHERE c1=t1.c1 GROUP BY c1 HAVING COUNT(DISTINCT c2) =1)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 08/30/2012 : 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
India
47099 Posts |
Posted - 08/30/2012 : 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/
|
|
|
| |
Topic |
|
Finding rows with matching values
