Select top 4 scores by person but need at least two locations

Question

I have data such as

eventId locationId score athlete
8739    73          48  matt
8734    73          46  matt
8788    73          45  matt
8738    73          44  matt
8787    73          44  matt
8735    73          43  matt
8789    6           43  matt

I need to capture the top 4 scores by person, but at least 1 of the top 4 scores needs to be from a different locationId than the other 3

In this case I would want this returned

eventId locationId score athlete
8739    73          48  matt
8734    73          46  matt
8788    73          45  matt
8789    6           43  matt

I've tried writing out queries that would use a GROUP BY HAVING MIN(locationId) != MAX(locationId) but I'm not sure how to accomplish that while also doing an ORDER BY and LIMIT.

I've also tried doing a self-join, but I'm not sure how to return the top results based on s.score and score2.

Start of a self-join that seems on the right track

SELECT s.eventid, s.locationid, athlete, score
, s2.eventid, s2.locationid, s2.athlete, score score2
FROM singles s
  INNER JOIN singles s2 ON s.athlete = s2.athlete AND s.locationid != s2.locationid
WHERE s.athlete = 'matt'
ORDER BY score DESC;

Answer 1

You may use row_number analytic function and limit clause including a self-join as the below one

select locationId, score, athlete
 from
   (
    select locationId, score, athlete, rn, rn2
      from(
            select *
              from
              (
                 with singles(locationId, score, athlete) as
                 (
                  select 73, 48, 'matt' union all
                  select 73, 46, 'matt' union all
                  select 73, 45, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 43, 'matt' union all
                  select  6, 43, 'matt'     
                 )
                 select row_number() over (partition by s.locationId order by s.score desc) as rn,
                        row_number() over (partition by s2.locationId order by s2.score desc) as rn2,
                        s.athlete, s.locationId, s.score
                   from singles s join singles s2 
                     on s.score = s2.score
                  where s.athlete = 'matt'
               ) q1
               order by score desc, rn, rn2 
            ) q2 
      group by locationId, score
     having sum(rn) <= sum(rn2)
      order by rn, score desc
      limit 4
   ) q3
 order by score desc

dbfiddle.uk demo

Answer 2

So, what you really want are the top three scores and then the first score after that that guarantees at least two locations.

This is a rather hard condition, but I think this does the trick:

with s as (
      select t.*,
             row_number() over (partition by athlete order by score desc) as seqnum
      from t
     ),
     s3 as (
      select s.*
      from s
      where seqnum <= 3
     )
select *
from s3
union all
(select s.*
 from s
 where ( (select count(distinct locationid) from s3) > 1 and seqnum = 4 ) or
       ( (select count(distinct locationid) from s3) = 1 and
         seqnum = (select min(seqnum)
                   from s
                   where locationid not in (select locationid from s3)
                  )
       )
);

Here is a db<>fiddle.