CODEWITHSUNDEEP
sql-server
user3196882
user3196882

Reputation: 1

using ROW_NUMBER() for 2 columns

Please need help to solve this issue I have a table Meeting

PersonOne   PersonTwo
Ajay        Bharat
Ajay        Elas
Chetan      Deepak
Deepak      Chetan
Chetan      Deepak
Farooq      Ajay
Anil        Sanjay
Gaganan     Hassan
Hassan      Isqh

Output should be 

PersonOne   PersonTwo    
Anil        Sanjay

If user wants to show only persons who have attended meeting only once here Bharat as attanded meeting only once but he had attended meeting with ajay, who had attended the meeting more then once. so Bharat should not show in the list

Output should be 

PersonOne   PersonTwo    
Chetan      Deepak
Deepak      Chetan
Chetan      Deepak

If user wants to show only persons who have attended meeting for third time I have tried almost all possible statements , like ,ROW_NUMBER() ,RANK() ,DENSE_RANK() ,NTILE but result..plz need your help

Upvotes: 0

Views: 77

Answers (2)

Keshavdas M
Keshavdas M

Reputation: 702

Please try the below query

SELECT Person
INTO #TEMP
FROM
(
   SELECT PersonOne as Person FROM tblMeeting
   UNION ALL 
   SELECT PersonTwo  as Person FROM tblMeeting
) P
GROUP By Person 
Having Count(Person) =1

select * from tblMeeting where 
PersonOne in ( select Person from #TEMP) and
PersonTwo in ( select Person from #TEMP)

Upvotes: 1

Akshay
Akshay

Reputation: 1901

The query below will take some time - no doubt, but definitely it will give you expected result.

SELECT * FROM tblMeeting WHERE (PersonOne IN
(
    SELECT PersonOne FROM 
    (
        SELECT PersonOne, COUNT(*) AS Expr1 FROM 
        (
            SELECT PersonOne FROM tblMeeting AS tblMeeting_3
            UNION ALL
            SELECT PersonTwo FROM tblMeeting AS tblMeeting_1
        ) AS derivedtbl_1 GROUP BY PersonOne
    ) AS derivedtbl_2 WHERE (Expr1 = 1)

)) 
AND (PersonTwo IN 
(
    SELECT PersonOne FROM 
    (
        SELECT PersonOne, COUNT(*) AS Expr1 FROM
        (
            SELECT PersonOne FROM tblMeeting AS tblMeeting_2
            UNION ALL
            SELECT PersonTwo FROM tblMeeting AS tblMeeting_1
        ) AS derivedtbl_1_1 GROUP BY PersonOne
    ) AS derivedtbl_2_1 WHERE (Expr1 = 1)
))

Here is Fiddle http://sqlfiddle.com/#!2/51b91/4

P.S : You can improve the query performance. :)

Upvotes: 1

Related Questions