CODEWITHSUNDEEP
mysqlsqlmariadb
user17644273
user17644273

Reputation:

Get single row number for record from mysql table by its position in sorted result set

I have a table that has two fields - id and counter. I want to sort all records by the counter field and then pick one record by its id and find out where in the entire table it is positioned by its counter. For example:

id counter
1 1
2 5
3 6
4 0
5 4

the ASC order by counter would be: 4, 1, 5, 2, 3.

And say I want to get the position for id=2, the value i am looking for is 4 because it is forth in the result set.

I have found some solutions that use ROW_NUMBER approach but those all seem to work with entire results. I just want this one specific value for specific row by its id.

I was trying to use a simpler solution that will simply store the rank in the table alongside the counter field by updating the table when counter changes:

UPDATE mytable 
SET counter_rank = ( SELECT COUNT(*) 
                     FROM mytable 
                     WHERE counter >= ( SELECT counter 
                                        FROM mytable 
                                        WHERE id = ? 
                                        GROUP BY counter)
                                       ) 
                     WHERE id = ?

which is nice solution but it does not work because if another counter changes, rank for all rows must be calculated, which is not a good idea for my case. So I have to look at the dynamic solution with row number approach.

Also grouping by the counter must be taken into account - multiple rows may share the position in the rankings by having the same counter value. so more than one row can return the same number/position.

Upvotes: 1

Views: 796

Answers (3)

ysth
ysth

Reputation: 98398

If you are getting multiple rows with their ranks, I would use ROW_NUMBER as you discovered, but in a subquery. And then select the desired rows from that subquery. But if you are getting just one row, this is most easily done with a join counting previous rows:

select mytable.id, mytable.counter, count(t2.id) counter_rank
from mytable
left join mytable t2 on t2.counter < mytable.counter
where mytable.id=2

(add 1 if you want a 1-based counter, not a 0-based counter)

fiddle

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

And if your Version hasent window function you can use a query like this:

SELECT FIND_IN_SET('2', GROUP_CONCAT(id ORDER BY counter)) AS position
FROM pos 
ORDER BY counter;

sample

MariaDB [test]> select * from pos;
+----+---------+
| id | counter |
+----+---------+
|  1 |       1 |
|  2 |       5 |
|  3 |       6 |
|  4 |       0 |
|  5 |       4 |
+----+---------+
5 rows in set (0.000 sec)

MariaDB [test]> SELECT FIND_IN_SET('2', GROUP_CONCAT(id ORDER BY counter)) AS position
    -> FROM pos 
    -> ORDER BY counter;
+----------+
| position |
+----------+
|        4 |
+----------+
1 row in set (0.000 sec)

MariaDB [test]>

Upvotes: 0

Akina
Akina

Reputation: 42728

SELECT id, counter,
       ROW_NUMBER() OVER (ORDER BY counter) position
FROM mytable
/* ORDER BY {needed expression} */
;

For specific row:

WITH cte AS (
    SELECT id, counter,
           ROW_NUMBER() OVER (ORDER BY counter) position
    FROM mytable
)
SELECT * FROM cte WHERE id = {needed value}
;

The query is not deterministic if counter is not unique, you must expand sorting expression in this case. For example, till ORDER BY couner, id.

Upvotes: 0

Related Questions