Problem with SQl query

Question

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 3-Oct-12 0:12am

SruthiR

Add a Solution

4 solutions

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

Posted 3-Oct-12 1:08am

damodara naidu betha

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)

Posted 3-Oct-12 1:29am

Vijay Walunj,Navi mumbai

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

Posted 5-Oct-12 1:27am

satz_770

Add a Solution

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

Devang Vaja · Accepted Answer · 2012-10-03T00:52:00

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

Posted 3-Oct-12 0:52am

Devang Vaja

Updated 3-Oct-12 18:27pm

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.

Problem with SQl query

4 solutions

Solution 2

Solution 3

Solution 5

Solution 7

Add your solution here

Preview 0

Problem with SQl query

4 solutions

Solution 2

Solution 3

Solution 5

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us