15,921,371 members
See more:
Hi,

I have table as given below.

Owner, Domain, ID
Alex - ABC - 111
Peter - PQR - 222
Alex - XYZ - 111
Alex - STU - 333

I need to check, If any of the Domain (column 2) under same Owner (column 1) has same ID (column 3). Then it should return the first corresponding row.

Here Alex's 2 Domains ABX and XYZ are having the same ID 111
So query should return
Alex ABC 111

Posted

Solution 2

SQL
select Top 1 Owner,Domain,ID from table where ID in(select Distinct ID From table where 1=1 Group By(ID) Having count(ID)>1)

now its updated 2nd time

v4
Vijay Walunj,Navi mumbai 3-Oct-12 7:31am
error:
Incorrect syntax near the keyword 'Having'

GRoup by missing
psychic6000 3-Oct-12 13:39pm
remove keyword "and" between where and group by

a hint to asker, why dont you just make unique(owner, domain, id) so there will be no repeated records.

Solution 3

Hi Shruti,

try this code block

SQL
SELECT Owner,Domain,Id FROM (
SELECT Owner,Domain,Id,ROW_NUMBER()
OVER(PARTITION BY Owner,Id ORDER BY ID ASC) ROWNUM FROM EmpDomain
) As A WHERE
A.ROWNUM < 2 ORDER BY ID

Thank you

Solution 5

SQL
select distinct o1.owner,o1.domain, o1.Id from owner o1
join owner o2 on o1.owner=o2.owner and o1.Id=o2.Id
where o1.domain=(select top 1 domain from  owner where id=o1.id and owner=o1.Owner)

Solution 7

Finally this meets ur expectation. Verify and mark as answer if it is,

select domain,owner,id from (select a.domain,a.owner,a.id, ROW_NUMBER() over(partition by a.id,a.owner order by a.domain) AS rowNumber
from domainTbl a, domainTbl b
where a.id = b.id
and a.owner = b.owner
and a.domain <> b.domain
)as Result
where rowNumber = 1

-------------
~~~Sathish~~~
-------------