Return the row with max value for each group

Question

I have the following table with name t2:

  realm   |    race    | gender | total  
----------+------------+--------+--------
 Buffalo  | faerie     | F      |   5972
 Buffalo  | faerie     | M      |   2428
 Buffalo  | footballer | F      |   1954
 Buffalo  | footballer | M      |   2093
 Buffalo  | raccoon    | F      |   2118
 Buffalo  | raccoon    | M      |   1237
 Buffalo  | shark      | F      |  12497
 Buffalo  | shark      | M      |   3621
 Buffalo  | wizard     | F      |    468
 Buffalo  | wizard     | M      |  11079
 Camelot  | faerie     | F      |   2414
 Camelot  | faerie     | M      |   1455

I want to create a query that just selects the realm, race and gender with the highest total. Every time I use GROUP BY I keep getting both genders.

The output table looks like this:

  realm   |    race    | gender | total  
----------+------------+--------+--------
 Buffalo  | faerie     | F      |   5972
 Buffalo  | footballer | M      |   2093
 Buffalo  | raccoon    | F      |   2118
...

I think I have a very poor understanding on how to compare rows.
I can't figure out how to write the WHERE clause so that when I GROUP BY realm,race,gender, I only get 1 gender.

You don't show what SQL produced the output. That would be helpful. — dougp, Nov 24 '20 at 20:10
What should the output look like? You say you want "the realm, race and gender with the highest total." You say you "keep getting both genders". Isn't the problem that you're getting 3 records when you expect 1? `Buffalo | shark | F | 12497` — dougp, Nov 24 '20 at 20:13
Your query doesn't even use the `adventure` table. How are all of the pieces of your "question" related? — dougp, Nov 24 '20 at 20:27
I wrote "adventure" as a generic table. I assumed that my query wouldnt be needed to answer the question. the table is "t2" — Kaasim Shaikh, Nov 24 '20 at 20:29
The second table, there are more records those are the first 3 — Kaasim Shaikh, Nov 24 '20 at 20:33

Erwin Brandstetter · Accepted Answer · 2020-11-24T20:37:47.833

1

A perfect use case for DISTINCT ON:

SELECT DISTINCT ON (realm, race) *
FROM   tbl
ORDER  BY realm, race, total DESC;

db<>fiddle here

Notably, the query has no GROUP BY at all.
Assuming total is NOT NULL, else append NULLS LAST.
In case of a tie, the winner is arbitrary unless you add more ORDER BY items to break the tie.

Detailed explanation:

Select first row in each GROUP BY group?

edited Nov 24 '20 at 20:37

answered Nov 24 '20 at 20:29

Erwin Brandstetter

605,456
145
1,078
1,228

Interesting strategy, it worked! Thank you very much! – Kaasim Shaikh Nov 24 '20 at 20:37

score 0 · Answer 2 · answered Nov 24 '20 at 20:41

0

select q.realm
, q.race
, q.gender
, q.total

from (
    Select t2.realm
    , t2.race
    , t2.gender
    , total
    , max(total) over (partition by t2.realm, t2.race) as maxtotal

    FROM adventure t2
) q
where q.total = q.maxtotal

answered Nov 24 '20 at 20:41

dougp

2,810
1
8
31

Return the row with max value for each group

2 Answers2