Skip to content
Advertisement

How to do MAX COUNT so Query returns 1 Result SQL

I have the following query:

Which returns the following results:

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

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:

Id EmailDomain UserCount
1 @gmail.com 4
2 @hotmail.com 1

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

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.

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement