如何进行最大计数，使查询返回1结果SQL

Question

我有以下查询：

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

返回以下结果：

<1><1><1><1>

Id	EmailDomain	UserCount
1	@yahoo.com
1	@gmail.com	4
2	@hotmail.com
3	@aol.com
3	@comcast.com

使用Dense_Rank()窗口函数也可以显示并列排名。

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

您可以在查询中使用MAX()和FIRST_VALUE()窗口函数：

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;