CODEWITHSUNDEEP
mysqlsqlselectmariadbwindow-functions
Luc
Luc

Reputation: 67

MySQL (MariaDB) get row position in ORDER BY

First of all, I already looked at this post but I couldnt get it to work.

So I wanna get the position of a row in my table with a group by argument. E.g. I have an table with various statistics in it. It looks like this.

+-----------+-------+--------+------+--------------+
| player_id | kills | deaths | wins | played_games |
+-----------+-------+--------+------+--------------+
|         1 |     0 |      2 |    1 |            3 |
|         2 |     0 |      1 |    0 |            1 |
|         3 |     0 |      0 |    0 |            0 |
|         5 |     0 |      1 |    0 |            1 |
|        13 |     0 |      1 |    0 |            1 |
|        14 |     3 |      1 |    2 |            3 |
|        29 |     0 |      3 |    0 |            3 |
|        30 |     0 |      1 |    0 |            2 |
|        32 |     5 |      0 |    1 |            1 |
+-----------+-------+--------+------+--------------+

So I wanna display the players rank position sorted by wins. In this example player 14 would be Rank #1.

I can do a simple SELECT * FROM tbl ORDER BY wins ASC then enumerate the rows, but it seems wasteful to load a potentially large resultset just for a single row.

Upvotes: 0

Views: 503

Answers (1)

GMB
GMB

Reputation: 222582

If you are running MariaDB 10.3 or higher, just want use window functions :

select t.*, rank() over(order by wins desc) rn
from mytable t
order by wins desc

rank() assigns the same score to players that have the same number of wins.

In earlier versions, one emulation option is a correlated subquery:

select
    t.*,
    (select count(*) + 1 from mytable t1 where t1.wins > t.wins) rn
from mytable t
order by wins desc

Upvotes: 1

Related Questions