MySQL Get Row Position in Order By

MySQL get row position in ORDER BY

Use this:

SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'

...to get a unique position value. This:

SELECT t.id,
       (SELECT COUNT(*)
          FROM TABLE x
         WHERE x.name <= t.name) AS position,
       t.name    
  FROM TABLE t      
 WHERE t.name = 'Beta'

...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...

MySQL (MariaDB) get row position in ORDER BY

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

mysql get row position of unique id

You don't need COUNT(*), since you're not trying to rank by a count, you just want to rank by a column value.

SELECT @rank := @rank + 1 AS rank, uid
FROM RANK
CROSS JOIN (SELECT @rank := 0) AS var
ORDER BY steps DESC

To get the rank of a specific uid, put this in a subquery.

SELECT rank FROM (
    SELECT @rank := @rank + 1 AS rank, uid
    FROM RANK
    CROSS JOIN (SELECT @rank := 0) AS var
    ORDER BY steps DESC) AS x
WHERE uid = '123545'

MySQL get row position in query

First of all I think you should add a DISTINCT keyword to your subquery. Because if we add bid=3 to the table it will get position = 5 with your query. So 4 will be missed.

SELECT p.*, 
      (SELECT count(DISTINCT bid)+1 
              FROM orders WHERE bid > p.bid) AS position 
      FROM orders p

SQLFiddle demo

Now we need to know position for new bid. Just use your subquery:

SELECT count(DISTINCT bid)+1 FROM orders WHERE bid > 4

mysql how to get row position with where clause

row_number() is applied on the result set according to the WHERE clause. Since you got only one record in that result, the row_number() is 1.

You first need to select the complete set, apply row_number() to it and then get the record with the searched number

SELECT position,
       phone
       FROM (SELECT row_number() OVER (PARTITION BY userid
                                       ORDER BY id ASC) AS position,
                    phone
                    FROM elbat) AS x
       WHERE phone = 44444;

If the table is large, it might be beneficial to limit the results of the derived table to the records of the user that has the searched number. An index on (phone, userid) and another one on (userid, id, phone) might support this.

SELECT position,
       phone
       FROM (SELECT row_number() OVER (ORDER BY id ASC) AS position,
                    phone
                    FROM elbat
                    WHERE userid = (SELECT userid
                                           FROM elbat
                                           WHERE phone = 44444)) AS x
       WHERE phone = 44444;

Mysql get position of row after sum and group

I am not sure if it can be much shorter, but the following query should give what you ask for:

SELECT rank
FROM (
    SELECT player_id, total_sum, @rank:=@rank+1 AS rank 
    FROM (
        SELECT player_id,  SUM(total_points) AS total_sum
        FROM players
        WHERE season_id=1 -- if you don't want to filter by season, remove this; or add more if you want to filter by other columns
        GROUP BY player_id
        ORDER BY total_sum DESC) AS sq, 
        (SELECT @rank:=0) AS tr
) AS q WHERE player_id = 1;

---

Explanation of the query

In the deepest subquery you calculate the sum of total points over the data you want:

SELECT player_id,  SUM(total_points) AS total_sum
FROM players
WHERE season_id=1 -- if you don't want to filter by season, remove this; or add more if you want to filter by other columns
GROUP BY player_id
ORDER BY total_sum DESC

You can leave out the WHERE or add more conditions. The last line shows that you order descending by the sum total, giving the highest first.

Next the previous query is used to calculate the rank using a variable @rank:

SELECT player_id, total_sum, @rank:=@rank+1 AS rank 
FROM (
...) AS sq, 
(SELECT @rank:=0) AS tr

which is initialized by:

SELECT @rank:=0

Finally, over this data you can select what you want to know, for example the rank of player_id=1, like shown in the first query.

Assign a value to a row from the position of a sort table

I would not recommend storing the rank in the table itself. This is a derived information, that can be computed on the fly whenever needed. On the other hand, storing it incurs maintaining it: you will find yourself creating stored procedures for each and every DML operation (update, insert, delete).

You could create a view instead. If you are running MySQL 8.0 or MariaDB >= 10.3, you can use window functions:

create view myview as
select 
    id, 
    score,
    rank() over(order by score desc) rnk
from mytable 

This gives you an always up-to-date perspective at your data, with 0 maintenance costs.

Note that rank is a SQL keyword, hence not a good choice for a column name - I named the column rnk instead.

---

Related Topics