Get total row number while only selecting a single row

Question

Related/similar questions: MySQL - Get row number on select, Select only partial result but get total number of rows

I currently have this table:

+----+--------------------+---------------+--------+
| id | accountId          | competitionId | rating |
+----+--------------------+---------------+--------+
|  1 | theidoftheaccount1 |            1  | 100    |
|  2 | theidoftheaccount2 |            3  | 90     |
|  3 | theidoftheaccount3 |            1  | 80     |
|  4 | theidoftheaccount4 |            1  | 50     |
+----+--------------------+---------------+--------+

I want to get the row with accountId='theidoftheaccount3', so I call the usual SQL statement SELECT * FROM competitors WHERE competitionId='someotherid1' AND accountId='theidoftheaccount3 ' ORDER BY rating DESC and all is good.

The problem: Now I want to to know the row number of the row I got but only out of all the other rows that have competitionId='someotherid1'. This row number would be the 'rank' of the competitor out of all the other competitors in the same competition.

So basically at the end of the day I would get back:

+----+--------------------+---------------+--------+-----------+
| id | accountId          | competitionId | rating | rowNumber |
+----+--------------------+---------------+--------+-----------+
|  3 | theidoftheaccount3 |            1  | 80     | 2         |
+----+--------------------+---------------+--------+-----------+

How can this be done?

Answer 1

One method is to use row_number() in a subquery:

select c.*
from (select c.*,
             rank() over (partition by competitionid order by rating desc) as ranking
      from competitors c
      where competitionId = 'someotherid1'
     ) c
where accountId = 'theidoftheaccount3';

EDIT:

An alternative without window functions is:

select count(*) + 1 as ranking
from competitors c
where c.competitionId = 'someotherid1' and
      c.rating > (select c2.rating
                  from competitors c2
                  where c2.competitionid = c.competitionId and
                        c2.accountId = 'theidoftheaccount3' 
                 );