CODEWITHSUNDEEP
postgresqlselectcountmax
Martin Johansen
Martin Johansen

Reputation: 63

PostgreSQL - Select multiple max counts

I'm using PostgreSQL.
I have a table competitor with information about a competitor in an e-sports game. The table contains gameID, the competitor's nick and name of the character he/she played.

I want to select every nick and the character they have played the most.
For example, if the competitor Faker has played the character Ryze 4 times and Lulu 3 times, I want Faker Ryze 4 to be in the output.

This is what I have so far:

select nick, character, count(*) as played
from competitor
group by nick, character
order by nick;

But then I get both Faker Ryze 4 and Faker Lulu 3 in the output.
I've tried with max() and nesting, but I can't figure it out.

Upvotes: 0

Views: 298

Answers (3)

Alexandros
Alexandros

Reputation: 2200

This will do the trick.

SELECT nick, character,played
FROM
  (SELECT s1.*,
          row_number() OVER (PARTITION BY nick
                             ORDER BY played DESC) AS row1
   FROM
     (SELECT nick, character, COUNT(*) AS played
      FROM competitor
      GROUP BY nick, character
      ORDER BY nick,
               COUNT(*) DESC) s1)s2
WHERE row1=1;

The query is 100% correct. SQL Fiddle here. Also, it is the only query that will easily support ties for the same nick (by changing row_number() with rank()) and avoids unnecessary JOIN.

Upvotes: 2

user1641172
user1641172

Reputation:

You are going down the right route with max and nesting, I have slightly modified an answer given here:

select t1.nick, t1.character, t1.count
from (
    select nick, character, count(*) as played
    from competitor
    group by nick, character
) t1
inner join
(
  select nick, max(played) max_count
  from 
  (
    select nick, character, count(*) as played
    from competitor
    group by nick, character
  )
  group by nick
) t2
  on t1.nick = t2.nick
  and t1.count = t2.max_count

This might be easier to read if you created a view for the base query:

View called PlayerCount:

select nick, character, count(*) as played
from competitor
group by nick, character

And the SQL query is now:

select t1.nick, t1.character, t1.count
from PlayerCount t1
inner join
(
  select nick, max(played) max_count
  from PlayerCount
  group by nick
) t2
on t1.nick = t2.nick
and t1.count = t2.max_count

Upvotes: 0

CFreitas
CFreitas

Reputation: 1775

Try this:

with counts as (
    select nick, character, count(*) cnt, max(sometimestamp) hora
    from competitor
    group by nick, character
    order by nick, cnt desc, hora
)


select nick, character, max(cnt)
from counts c1 where nick
in (select nick from counts where nick = c1.nick limit 1)
    and character in (select character from counts where nick = c1.nick limit 1)
group by nick, character
order by nick

max(sometimestamp) hora will be the tie breaker, you will have to adapt. Or if you don't care for ties just remove this field. This query will give you the character for each nick, with the bigger count, and, in case of tie, it will return the first one to reach the max count.

Upvotes: 1

Related Questions