0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Luc
  • 57
  • 1
  • 8
  • Use ROW_NUMBER() or RANK() or DENSE_RANK() in CTE (depends on needed logic) and select obtained number in main query. – Akina Jun 02 '20 at 17:38
  • See [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 02 '20 at 17:38

1 Answers1

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    This one worked for me! With a little bit more research. because I wanted a WHERE clausel in there, I came up with this ```WITH rank AS ( SELECT t.wins,t.player_id, RANK() OVER(ORDER BY wins DESC) rn FROM `table_name` t ORDER BY wins DESC ) SELECT rn FROM rank WHERE player_id = '2'``` – Luc Jun 02 '20 at 22:47