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?

You must enumerate records (using variables or self-join) first, and then apply filtering. — Akina, Jan 05 '20 at 19:30
I would like this to be as scalable as possible, what would be the fastest/most efficient way? I expect this table to grow very quickly. — ZeroByter, Jan 05 '20 at 19:39

Gordon Linoff · Accepted Answer · 2020-01-05T22:20:05.347

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' 
                 );

edited Jan 05 '20 at 22:20

answered Jan 05 '20 at 19:04

Gordon Linoff

1,242,037
58
646
786

Window functions were first introduced in MariaDB 10.2.0. – Akina Jan 05 '20 at 19:25
@Akina . . . which was close to three years ago. When I answered, there was no indication of the MariaDB version. – Gordon Linoff Jan 05 '20 at 22:17
Thanks for the alternative! Unfortunately I won't have access to my computer for a little while, so it'll be a while before I can try and implement this and see if it works. I also just now realized how outdated my dev environment is and I managed to update it. – ZeroByter Jan 06 '20 at 06:16
After I updated my XAMPP setup, both setups work, although I found in general the alternative solution you provided is faster. Many thanks! – ZeroByter Jan 09 '20 at 12:13

score 1 · Answer 2 · answered Jan 05 '20 at 21:27

1

If your database does not support window functions, an alternative is to use a subquery:

select
    t.*,
    (
        select count(*) + 1
        from mytable t1
        where t1.competitionId = t.competitionId and t1.rating > t.rating
    ) row_num
from mytable t
where t.accountId = 'theidoftheaccount3'

For performance, you want an index on columns (competitionId, rating) (and another on column accountId, but it is possibly already there since this looks like a unique column).

answered Jan 05 '20 at 21:27

GMB

216,147
25
84
135

Thanks for the alternative! In my table only column `id` is unique. Unfortunately I won't have access to my computer for a little while, so it'll be a while before I can try and implement this and see if it works. – ZeroByter Jan 06 '20 at 06:12

Get total row number while only selecting a single row

2 Answers2