Problem
Using MySQL, create the following table:
+-----------------------------+
+ id INT UNSIGNED +
+ name VARCHAR(100) +
+-----------------------------+
How can I select a single row AND its position amongst the other rows in the table, when sorted by name ASC. So, if the table data is sorted by name, it looks like this:
+-----------------------------+
+ id | name +
+-----------------------------+
+ 5 | Alpha +
+ 7 | Beta +
+ 3 | Delta +
+ ..... +
+ 1 | Zed +
+-----------------------------+
How can I retrieve the current location of the Beta row by selecting it? I’m searching for a collection of results that looks like this:
+-----------------------------+
+ id | position | name +
+-----------------------------+
+ 7 | 2 | Beta +
+-----------------------------+
I can do a simple SELECT * FROM tbl ORDER BY name ASC then enumerate the rows in PHP, but it seems wasteful to load a potentially large resultset just for a single row.
Asked by leepowers
Solution #1
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 achieve a one-of-a-kind 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 assign the same value to ties. IE: If two values are in second place, they’ll both have a position of 2 when the first query gives one of them a position of 2 and the other a position of 3…
Answered by OMG Ponies
Solution #2
This is the only option that comes to mind:
SELECT `id`,
(SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
`name`
FROM `table`
WHERE `name` = 'Beta'
Answered by zerkms
Solution #3
If the question is basic but the size of the returned result set is likely to be enormous, consider splitting it into two inquiries.
The first question employs a narrow-down filtering criteria to only obtain data for that row, whereas the second query calculates the position using COUNT with the WHERE clause.
In your instance, for example.
Query 1:
WHERE name = ‘Beta’, SELECT * FROM tbl
Query 2:
WHERE name >= ‘Beta’, SELECT COUNT(1) FROM tbl
This solution is far more scalable than OMG Ponies’ approach in a table with 2M records.
Answered by Max
Solution #4
The other options appear to be too complex for me.
Let’s take a simple example: suppose you have a table with columns:
userid | points
and you want to get the row position (the user’s “ranking”) by sorting the userids by points, then use:
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, userid, points
FROM
ourtable
ORDER BY points DESC
The row position is given by num (ranking).
If you have MySQL 8.0 or higher, you should consider using ROW NUMBER ()
Answered by Avatar
Solution #5
The number of rows “better” than the targeted row is represented by a row’s position in the table.
As a result, you must count the rows.
WHERE name’Beta’ IS COUNT(*)+1 FROM table
In the event of a tie, the highest rank is returned.
If you add another row with same name of “Beta” after the existing “Beta” row, then the position returned would be still 2, as they would share same place in the classification.
I hope this helps anyone who are looking for something similar in the future, as I believe the question’s author has already found a solution to his problem.
Answered by NVG
Post is based on https://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by