I have the following query:

SELECT
    Id,
    EmailDomain,
    COUNT(Users) AS UserCount
FROM 
    Table_Moragn
GROUP BY 
    Id, EmailDomain

Which returns the following results:

I need the Ids and the Email Domains for a later query, but I don't want multiple email domains. So I want my results to look like this:

Original I thought to do a Max Count either through a subquery or having by

SELECT
    Id,
    EmailDomain,
    COUNT(Users) AS UserCount
FROM 
    Table_Morgan
GROUP BY
    Id, EmailDomain
HAVING
    COUNT(Users) = (SELECT MAX(UserCount)
                    FROM (EmailDomain, COUNT(Users) as UserCount
                    FROM Table_Morgan
                    GROUP BY EmailDomain) AS T)

but I get an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Or says it can't return multiple results with an =.

A problem I see with this in cases like ID 3, where the count is the same number.

I tried adding LIMIT at the end of my query but SQL doesn't like it.

CodePudding user response：

Use Dense_Rank() windowed function to display tied rank as well.

SELECT   -- main result
  *
FROM (SELECT    -- ranking here
  *,
  DENSE_RANK() OVER (PARTITION BY Id ORDER BY UserCount DESC, EmailDomain) rnk
FROM (SELECT  -- group count here
  Id,
  EmailDomain,
  COUNT(Users) AS UserCount
FROM Table_Moragn
GROUP BY Id,
         EmailDomain) x) y
WHERE y.rnk = 1

CodePudding user response：

You can use MAX() and FIRST_VALUE() window functions in your query:

SELECT DISTINCT Id,
       FIRST_VALUE(EmailDomain) OVER (PARTITION BY Id ORDER BY COUNT(Users) DESC) EmailDomain,
       MAX(COUNT(Users)) OVER (PARTITION BY Id) UserCount
FROM Table_Moragn
GROUP BY Id, EmailDomain;