CODEWITHSUNDEEP
postgresql
Liondancer
Liondancer

Reputation: 16469

postgres query to return only id and count with max count

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

Upvotes: 0

Views: 324

Answers (1)

Traveling Man
Traveling Man

Reputation: 105

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)

Upvotes: 1

Related Questions