Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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 3-Oct-12 0:12am
SruthiR1.3K
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
v4
Comments
Vijay Walunj,Navi mumbai at 3-Oct-12 7:31am
   
error:
Incorrect syntax near the keyword 'Having'
 
GRoup by missing
psychic6000 at 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: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 398
1 CPallini 333
2 George Jonsson 270
3 OriginalGriff 159
4 Prasad Khandekar 135
0 OriginalGriff 4,613
1 CPallini 3,340
2 Sergey Alexandrovich Kryukov 2,771
3 George Jonsson 2,254
4 Gihan Liyanage 2,020


Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 5 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100