CODEWITHSUNDEEP
sqlpostgresql
alh
alh

Reputation: 2599

How to get all max count with a field

I am trying to return the names with the max number of entries in a table and return a list of (name, count) tuples for those with the max for count. My current solution uses:

select name, count(*)
from action_log
group by name
order by count desc
limit 1;

The problem is that using limit 1 does not account for multiple names having a max count value.

How can I determine the max count and then get ALL matching names? I want to (but can't obviously) do something like:

select name, max(count(*))
from action_log
group by name;

Upvotes: 2

Views: 711

Answers (5)

roman
roman

Reputation: 117420

you can do this with rank() function, so you don't have to query table multiple times:

with cte as (
    select
        name, count(*) as total,
        rank() over(order by count(*) desc) as rnk
    from action_log
    group by name
)
select name, total
from cte
where rnk = 1

Even better, you can dense_rank(), so you can take n groups or take nth group:

with cte as (
    select
        name, count(*) as total,
        dense_rank() over(order by count(*) desc) as rnk
    from action_log
    group by name
)
select name, total
from cte
where rnk <= 2 -- or rnk in (1, 2), or rnk = 2 and so on

sql fiddle demo

Upvotes: 0

evilone
evilone

Reputation: 22740

You can do this with subquery

For example:

SELECT MAX(cnt) FROM
(SELECT name, count(*) AS cnt
FROM action_log
GROUP BY name) AS gb

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

SQL Fiddle

with s as (
    select name, count(*) as total
    from action_log
    group by name
), m as (
    select max(total) as max_total from s
)
select name, total
from s
where total = (select max_total from m)

Upvotes: 3

Vulcronos
Vulcronos

Reputation: 3456

Try this:

select name, COUNT(*)
from action_log
group by name
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) from action_log group by name ORDER BY COUNT(*) DESC)

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

You could do this with sub queries - except there are some rules surrounding the group by. How about simplifying it with a view:

create view cname as
    select name, count(name) c
    from action_log
    group by name

and then SELECT like this:

select distinct a.name
from action_log a
  join cname c on c.name = a.name
where c.c = (select max(c) from cname)

and here is a SQL Fiddle to prove it.

Upvotes: 1

Related Questions