CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Kaasim Shaikh
Kaasim Shaikh

Reputation: 23

Return the row with max value for each group

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.

Upvotes: 0

Views: 320

Answers (2)

dougp
dougp

Reputation: 3089

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658767

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:

Upvotes: 1

Related Questions