How to get rows having maximum occurrence using SQL-CodePudding

I have a participant table:

Participant table

I have written a CTE which gives the following table:

CTE output table

CTE code:

with cte as
(
select t.per1,t.per2,t.met1,concat(t.per1,t.per2) concated from
(
select p1.person_id per1, p1.meeting_id met1, p2.person_id per2 from participant p1 cross join participant p2
where p1.meeting_id=p2.meeting_id and p1.person_id<>p2.person_id
)t
where t.per1<t.per2
--order by t.per1,t.per2
)

Now I would like to fetch the rows from this cte where the count of concated is maximum. i.e I want the rows

Per1 Per2 Met1 Concated
1     2    10     12
1     2    20     12
1     2    30     12

since concated=12 occurs maximum number of times

How do I achieve the same? I tried using count and group by but not able to return Per1, Per2 and Met1 all together.

Thanks in advance.

CodePudding user response：

We can use the COUNT() analytic in your innermost query to find the counts of pairs of meeting attendees:

WITH cte AS (
    SELECT p1.person_id per1, p1.meeting_id met, p2.person_id per2,
           COUNT(*) OVER (PARTITION BY p1.person_id, p2.person_id) cnt
    FROM participant p1
    INNER JOIN participant p2
        ON p1.meeting_id = p2.meeting_id AND p1.person_id < p2.person_id
)

SELECT TOP 1 WITH TIES met, per1, per2
FROM cte
ORDER BY RANK() OVER (ORDER BY cnt DESC);