Selecting and ordering by the top from each country

I am scoring events where there are 2 individuals from each country in an event, BUT only the top finisher from a country receives points. If a country finishes 1st and 2nd, the 2nd place is dropped and the third place country moves up to 2nd place. Data:

competitorid    wristbandid countryname eventid place
14905           8           Australia       100 1
14923           26          New Zealand     100 2
14910           13          Australia       100 3
14922           25          New Zealand     100 4
14926           29          South Africa    100 5
14939           42          Great Britain   100 6
14901           4           United States   100 7
14914           17          Japan           100 8
14903           6           United States   100 9
14940           43          Great Britain   100 10
14933           36          Canada          100 11
14929           32          South Africa    100 12
14917           20          Japan           100 13
14934           37          Canada          100 14

I’ve tried using a couple of things like: ROW_NUMBER() OVER(PARTITION BY

SELECT ROW_NUMBER() OVER(PARTITION BY countryname ORDER BY eventid,countryname,place asc)
  AS Row,*
  FROM  competitormainviewpoints2021isrc ORDER BY row,eventid,place ASC

I know this is wrong and I’m not getting only the top athletes from each country.

Answer

I believe you need to partition by the event ID as well. Something like:

SELECT competitorid
    , wristbandid
    , countryname
    , eventid
    , place
    , RANK() OVER(PARTITION BY countryname, eventid, order by place DESC) AS RNK
FROM competitormainviewpoints2021isrc

Advertisement

Answer