MySQL 获取 ORDERBY 中的行位置

This is the only way that I can think of:

SELECT `id`,
(SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
`name`
FROM `table`
WHERE `name` = 'Beta'

may be what you need is with add syntax

LIMIT

so use

SELECT * FROM tbl ORDER BY name ASC LIMIT 1

if you just need one row..

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...

If the query is simple and the size of returned result set is potentially large, then you may try to split it into two queries.

The first query with a narrow-down filtering criteria just to retrieve data of that row, and the second query uses COUNT with WHERE clause to calculate the position.

For example in your case

Query 1:

SELECT * FROM tbl WHERE name = 'Beta'

Query 2:

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

We use this approach in a table with 2M record and this is way more scalable than OMG Ponies's approach.

The position of a row in the table represents how many rows are "better" than the targeted row.

So, you must count those rows.

SELECT COUNT(*)+1 FROM table WHERE name<'Beta'

In case of a tie, the highest position 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.

Hope this helps people that will search for something similar in the future, as I believe that the question owner already solved his issue.

I've got a very very similar issue, that's why I won't ask the same question, but I will share here what did I do, I had to use also a group by, and order by AVG. There are students, with signatures and socore, and I had to rank them (in other words, I first calc the AVG, then order them in DESC, and then finally I needed to add the position (rank for me), So I did something Very similar as the best answer here, with a little changes that adjust to my problem):

I put finally the position (rank for me) column in the external SELECT

SET @rank=0;
SELECT @rank := @rank + 1 AS ranking, t.avg, t.name
FROM(SELECT avg(students_signatures.score) as avg, students.name as name
FROM alumnos_materia
JOIN (SELECT @rownum := 0) r
left JOIN students ON students.id=students_signatures.id_student
GROUP BY students.name order by avg DESC) t

The other answers seem too complicated for me.

Here comes an easy example, let's say you have a table with columns:

userid | points

and you want to sort the userids by points and get the row position (the "ranking" of the user), then you use:

SET @row_number = 0;


SELECT
(@row_number:=@row_number + 1) AS num, userid, points
FROM
ourtable
ORDER BY points DESC

num gives you the row postion (ranking).

If you have MySQL 8.0+ then you might want to use ROW_NUMBER()

I was going through the accepted answer and it seemed bit complicated so here is the simplified version of it.

SELECT t,COUNT(*) AS position FROM t
WHERE name <= 'search string' ORDER BY name

I have similar types of problem where I require rank(Index) of table order by votes desc. The following works fine with for me.

Select *, ROW_NUMBER() OVER(ORDER BY votes DESC) as "rank"
From "category_model"
where ("model_type" = ? and "category_id" = ?)