How to find out the sequence number of a record from a selection of two tables?

Question

PHP

Request type

SELECT t1.target_id, SUM(t1.cnt) AS hm, t2.id, t2.name FROM votes AS t1, people AS t2 WHERE t1.target_id=t2.id GROUP BY t2.id ORDER BY hm DESC, t2.name ASC LIMIT 10

Now this query returns a list of people, sorted by the number of their "rank" on the site. Through the cycle, it is not difficult to find out their position in the ranking, but it is very necessary that it is displayed directly in the query in some separate column, such as position. In order to later show the user's place in the rating in the user's profile.
We need to find out the number of each record after they have been sorted and displayed. Tried this from Google , but it displays some wrong numbers. I think this happens because of GROUP.@row:[email protected]+ 1

Answer 1

We need to wrap the query in another SELECT

SET @row = 0;
SELECT *, @row := @row + 1 AS `pos`
  FROM (
    SELECT `t1`.`id`, `t1`.`hm`, `t2`.`name`
      FROM (
        SELECT `target_id` AS `id`, SUM(`cnt`) AS `hm`
          FROM `votes`
          GROUP BY `target_id`
      ) AS `t1`
      JOIN `people` AS `t2` ON `t2`.`id` = `t1`.`id`
      ORDER BY `t1`.`hm` DESC, `t2`.`name` ASC 
      LIMIT 10
  ) AS `x`