Click here to Skip to main content
15,921,371 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
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

Please help on creating this query

Thanks in advance
Posted

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
 
Share this answer
 
v4
Comments
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.
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
 
Share this answer
 
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)
 
Share this answer
 
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~~~
-------------
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900