Find names based on max number dynamically in SQL-CodePudding

I have a data set like this:

Table A:

Date           PlaceName              Partner             Money
2021-03-26      SITE A               PARTNER A           100
2021-04-26      SITE B               PARTNER A           200
2021-03-26      SITE A               PARTNER B           0
2021-04-26      SITE B               PARTNER B           230
2021-04-26      SITE B               PARTNER B           230
2021-03-26      SITE A               PARTNER C           0
2021-04-26      SITE B               PARTNER C           230
2021-04-26      SITE B               PARTNER C           230
...

What's the maximum number of Place on which a Partner has spent money and who are these Partners? Display only the Partners reaching this max number

I tried this:

select count(PlaceName) as num_of_sites, Partner
from (
    select distinct Place, Partner
    from TableA
    where Money > 0
) a
group by Partner
order by count(PlaceName) desc

But I feel like its not the right logic. What am I missing?

CodePudding user response：

There's really no need for your subquery, it works without it:

SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
FROM TableA a
WHERE a.Money > 0 
GROUP BY a.Partner
ORDER BY COUNT(DISTINCT a.PlaceName) desc

but it may be more efficient to have one depending on the data, if recounting distinct rows is too expensive:

SELECT b.* 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
    FROM TableA a
    WHERE a.Money > 0 
    GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc

If you only want the name on the highest result(s) then you want to:

SELECT TOP 1 WITH TIES b.PARTNER 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
    FROM TableA a
    WHERE a.Money > 0 
    GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc