title: SQL ranking multiple groups
body: <p>For a project I was asked to design a DB for a small Olympic games. This is the ER diagram I made for the DB. </p>
  
  <p><a href="https://i.stack.imgur.com/ePxG2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ePxG2.png" alt="enter image description here"></a></p>
  
  <p>For one of the required queries, I need to list the sport, event, name, country, and result(medal) for everyone. So far the only problem I am having is ranking(assigning GOLD, SILVER, or BRONZE) each of the results based only on others in the same event. </p>
  
  <p>My query so far:</p>
  
  <pre><code>SELECT cy.countryname COUNTRY, 
         c.firstname || ' ' || c.lastname "COMPETITOR", 
         s.sportname, 
         e.eventname EVENT, 
         rs.result
     FROM COUNTRY cy
     JOIN COMPETITOR c ON cy.COUNTRYID = c.COUNTRYID
     JOIN RESULT rs ON c.competitorid = rs.competitorid
     JOIN EVENT e ON rs.eventid = e.eventid
     JOIN SPORT s ON e.sportid = s.sportid
     ORDER BY e.eventname, rs.result;
  </code></pre>
  
  <p>Produces:</p>
  
  <pre><code>UNITED STATES   NORRIS HOLMWOOD SWIMMING        100 METER BUTTERFLY 49.82
  ITALY           LEANDRO ROCCO   SWIMMING        100 METER BUTTERFLY 50.65
  ARGENTINA       ORFEO SILVA     SWIMMING        100 METER BUTTERFLY 50.69
  IRAN            HAMUND NAMAD    SWIMMING        100 METER BUTTERFLY 51.2
  CHINA           MU KWOK         SWIMMING        100 METER BUTTERFLY 51.32
  RUSSIA          MITRODFAN KRUPIN SWIMMING       100 METER BUTTERFLY 52.01
  ARGENTINA       NICOLAO VARELA  TRACK AND FIELD 100 METER DASH      9.76
  ITALY           STEFANO PAVONI  TRACK AND FIELD 100 METER DASH      9.98
  UNITED STATES   ROBBY TURNBULL  TRACK AND FIELD 100 METER DASH      10.1
  RUSSIA          IRINEY POLZIN   TRACK AND FIELD 100 METER DASH      10.35
  CHINA           TU JIANG        TRACK AND FIELD 100 METER DASH      10.54
  IRAN            SAKHR NAGI      TRACK AND FIELD 100 METER DASH      10.56
  UNITED STATES   SCARLETT NOWELL SWIMMING        200 METER BACKSTROKE 116.32
  IRAN            MALIKA NEJEM    SWIMMING        200 METER BACKSTROKE 116.88
  etc...
  </code></pre>
  
  <p>How would I go about assigning each competitor a GOLD, SILVER, BRONZE, or NO MEDAL based only on the other results in the same event?</p>