Count rows with max per group and additional condition I have the following table:

 horse_main_id | race_id |    horse_name     | rating100 | race_result 
---------------+---------+-------------------+-----------+-------------
            23 |     159 | Angelology        |       126 |           1
            24 |     159 | Arwoc             |       118 |            
            25 |     159 | Marlo Man         |       118 |            
            26 |     159 | Hucknall          |       113 |            
            27 |     159 | It's Electrifying |       110 |           2
            28 |     159 | Monte Carlo       |       107 |            
            29 |     159 | Cross Constance   |       103 |            
            30 |     160 | Auld Burns        |       119 |            
            31 |     160 | Diamond Jim       |       117 |            
            33 |     160 | Livery            |       115 |            
            34 |     160 | Paraggi           |       115 |            
            35 |     160 | I'll'ava'alf      |       110 |           2
            36 |     160 | Jacks 'n' Kings   |       108 |            
            32 |     160 | Fastnet Isle      |       115 |           1


I would like to operate on groups of race_id, find all groups that have the max(rating100) for their group and race_result = 1, and count all the times this happens.

So this example should return only 1, as for race_id 159 group the max rating100 of 126 also has race_result = 1, but for race_id 160 group, the max rating100 is 119, but does not have race_result = 1.

This is the closest I've got (was told I should use window functions):

SELECT * FROM (
    SELECT horse_name, race_id, race_result
         , max(rating100) OVER (partition by race_id) AS max_rating
    FROM horse_main) t
WHERE race_result = 1;
Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper):

SELECT count(*)
FROM  (SELECT race_id, max(rating100) AS rating100
       FROM   horse_main
       GROUP  BY 1) x
JOIN   horse_main h USING (race_id, rating100)
WHERE  h.race_result = 1;


If there are many rows per race_id, it will be faster to get group-wise maxima with one of these techniques:


Optimize GROUP BY query to retrieve latest record per user




Or use the window function rank() - that's what you count effectively: all greatest rating100 per race_id with race_result = 1:

SELECT count(*)
FROM  (SELECT race_result, rank() OVER (PARTITION BY race_id ORDER BY rating100 DESC) AS rnk
       FROM   horse_main) x
WHERE  rnk = 1
AND    race_result = 1;


An index on (race_id, rating100 DESC, race_result DESC) should help performance with big tables.

Assuming rating100 is defined NOT NULL, else you need to add NULLS LAST in query and index.