12,633,336 members (32,880 online)
Rate this:
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 3-Oct-12 1:12am
SruthiR1.1K

Rate this:

## Solution 2

`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.
Rate this:

## Solution 3

Hi Shruti,

try this code block

```
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
Rate this:

## Solution 5

```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)```
Rate this:

## 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~~~
-------------

Top Experts
Last 24hrsThis month
 OriginalGriff 290 Peter Leow 185 CHill60 125 Jochen Arndt 80 F-ES Sitecore 72
 OriginalGriff 2,124 ppolymorphe 1,252 Peter Leow 979 John Simmons / outlaw programmer 695 CPallini 621