PostgreSQL - Select multiple max counts

Question

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.

Alexandros · Accepted Answer · 2015-10-21 15:23:16Z

2

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.

edited Oct 21, 2015 at 15:23

answered Oct 21, 2015 at 14:38

Alexandros

2,2004 gold badges27 silver badges57 bronze badges

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

6 Comments

CFreitas Over a year ago

this will result in all counts. you are computing max on nick and character witch were already unique after the nested select.

Martin Johansen Over a year ago

I'm afraid not. This does exactly the same as the query I had from before.

Alexandros Over a year ago

@MartinJohansen Fixed it.

CFreitas Over a year ago

You are making the same mistake. you are partitioning by nick and character, witch are already unique from the inner select (s1). All row1 will be "1".

CFreitas Over a year ago

I see "PARTITION BY nick, character"

|

CFreitas · Accepted Answer · 2015-10-21 14:44:17Z

1

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.

answered Oct 21, 2015 at 14:44

CFreitas

1,79520 silver badges29 bronze badges

1 Comment

Martin Johansen Over a year ago

Thank you very much. This worked perfectly! I didn't even know about WITH statements.

Community · Accepted Answer · 2017-05-23 12:23:34Z

0

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

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Oct 21, 2015 at 13:55

user1641172

2 Comments

Martin Johansen Over a year ago

i get this error: ERROR: aggregate functions are not allowed in JOIN conditions LINE 15: and t1.count = t2.max_count;

user1641172 Over a year ago

ah yeah sorry I left in the name of the dummy table I tested it on, I have modified it now.

Collectives™ on Stack Overflow

PostgreSQL - Select multiple max counts

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related