title: Query to rank rows in groups
body: <p>I'm using Apache Derby 10.10.</p>
  
  <p>I have a list of participants and would like to calculate their rank in their country, like this:</p>
  
  <pre><code>|        Country |         Participant | Points | country_rank |
  |----------------|---------------------|--------|--------------|
  |      Australia |      Bridget Ciriac |      1 |            1 |
  |      Australia |     Austin Bjorklun |      4 |            2 |
  |      Australia |        Carrol Motto |      7 |            3 |
  |      Australia |     Valeria Seligma |      8 |            4 |
  |      Australia |     Desmond Miyamot |     27 |            5 |
  |      Australia |      Maryjane Digma |     33 |            6 |
  |      Australia |       Kena Elmendor |     38 |            7 |
  |      Australia |         Emmie Hicke |     39 |            8 |
  |      Australia |        Kaitlyn Mund |     50 |            9 |
  |      Australia |    Alisia Vitaglian |     65 |           10 |
  |      Australia |          Anika Bulo |     65 |           11 |
  |             UK |          Angle Ifil |      2 |            1 |
  |             UK |     Demetrius Buelo |     12 |            2 |
  |             UK |      Ermelinda Mell |     12 |            3 |
  |             UK |         Adeline Pee |     21 |            4 |
  |             UK |     Alvera Cangelos |     23 |            5 |
  |             UK |   Keshia Mccalliste |     23 |            6 |
  |             UK |        Alayna Rashi |     24 |            7 |
  |             UK |    Malinda Mcfarlan |     25 |            8 |
  |  United States |     Gricelda Quirog |      3 |            1 |
  |  United States |      Carmina Britto |      5 |            2 |
  |  United States |         Noemi Blase |      6 |            3 |
  |  United States |        Britta Swayn |      8 |            4 |
  |  United States |        An Heidelber |     12 |            5 |
  |  United States |        Maris Padill |     21 |            6 |
  |  United States |     Rachele Italian |     21 |            7 |
  |  United States |   Jacquiline Speake |     28 |            8 |
  |  United States |      Hipolito Elami |     45 |            9 |
  |  United States |          Earl Sayle |     65 |           10 |
  |  United States |       Georgeann Ves |     66 |           11 |
  |  United States |       Conchit Salli |     77 |           12 |
  </code></pre>
  
  <p>The schema looks like this (<a href="http://sqlfiddle.com/#!5/f48f8/4" rel="nofollow">sqlfiddle</a>):</p>
  
  <pre><code>create table Country(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    name varchar(255),
    PRIMARY KEY (id)
  );
  
  create table Team(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    country_id int not null,
    PRIMARY KEY (id),
    FOREIGN KEY (country_id) REFERENCES Country(id)
  );
  
  create table Participant(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    team_id int not null,
    name varchar(100),
    points int,
    PRIMARY KEY (id),
    FOREIGN KEY (team_id) REFERENCES Team(id)
  );
  </code></pre>
  
  <p>This is what I have tried:</p>
  
  <pre><code>select
      Country.name,
      Participant.name,
      Participant.points,
      ROW_NUMBER() OVER(order by Country.name, Participant.points) as country_rank
  from Country
  join Team
      on Country.id = Team.country_id
  join Participant
      on Team.id = Participant.team_id;
  </code></pre>
  
  <p>But according to the <a href="https://db.apache.org/derby/docs/10.10/ref/index.html" rel="nofollow">apache derby doco</a>, the OVER() statement doesn't take any arguments.</p>
  
  <p>Does anyone have a way to achieve the country rank?</p>