postgres query to return only id and count with max count

Question

How can I display ONLY id 4 and 2 because they have the highest count?

SELECT id, count(*) FROM followers GROUP BY id ORDER BY 2 DESC;

id count
4  4
2  4
1  3
3  2

expected outout

id count
4  4
2  4

Traveling Man · Accepted Answer · 2018-03-15 16:43:39Z

1

You could use a Common Table Expression (CTE) like below:

 WITH counts AS ( 
 SELECT id, count(*) AS cnt FROM followers GROUP BY id)
 SELECT * FROM counts where cnt = (select MAX(cnt) from counts)

answered Mar 15, 2018 at 16:43

Traveling Man

1054 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1