Select rows with the highest value for one ID, after grouping by two IDs

It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.

SELECT I.AgentID, I.PersonID, Count(*) AS BustCount
FROM InvolvedIn I
WHERE I.AgentID IS NOT NULL 
AND I.isCulprit = TRUE
GROUP BY I.AgentID, I.PersonID HAVING COUNT(*) > 1;

This what I get:

AID     PID     BustCount 
1       11      4
2       22      2
3       22      5
4       33      5
5       44      4

But I would like to get only the PID with the highest BustCount (not line row 2).

AID     PID     BustCount 
1       11      4
3       22      5
4       33      5
5       44      4

Simply adding DISTINCT ON (PersonID) will do the job in PostgreSQL:

SELECT DISTINCT ON (PersonID)
       AgentID, PersonID, Count(*) AS BustCount
FROM   InvolvedIn i
WHERE  AgentID IS NOT NULL 
AND    isCulprit
GROUP  BY AgentID, PersonID
HAVING COUNT(*) > 1
ORDER  BY PersonID, BustCount DESC, AgentID;

I added AgentID as (optional) third ORDER BY expression and tiebreaker to get a deterministic result in case of multiple agents with the same amount of busts. Adapt to your requirements.

Detailed explanation:

• Select first row in each GROUP BY group?

We can do all of it in a single SELECT without subquery, because DISTINCT ON is applied after aggregation. About the sequence of events in an SELECT query:

• Best way to get result count before LIMIT was applied

Oh, and WHERE isCulprit = TRUE is just a more noisy way of saying WHERE isCulprit.

StackExchange Database Administrators Q#286975, answer score: 2