Как узнать порядковый номер записи из выборки из двух таблиц?

Question

[Albert Kazan]

Запрос типа

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

Сейчас этот запрос выводит список людей, сортируя их по количеству их "рейтинга" на сайте. Через цикл узнавать их позицию в рейтинге не составляет труда, но очень надо чтоб прям в запросе выводилось в какой-нибудь отдельный столбец, типа position. Для того, чтобы потом в профиле пользователя показывать его место в рейтинге.
Нужно узнать номер каждой записи после того, как они были отсортированы и выведены. Пробовал из гугла это @row:=@row+ 1, но оно выводит какие-то неверные числа. Думаю так происходит из-за GROUP.

Answer 1

[Rsa97]

Надо обернуть запрос ещё в один 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`

Comments

[Albert Kazan]

Благодарю! Сейчас попробую

[Albert Kazan]

Неправильно, к сожалению.
В 'pos' просто выводится id пользователя, а не порядковый номер в сортировке.

[Rsa97]

Albert Tatarin, там не id пользователя, но ошибка есть, row считается до сортировки. Поправил.

[Albert Kazan]

Rsa97, работает. Ты - гений в этой сфере.
Надеюсь, ты пожалеешь дурачка и ответишь на вторую половину вопроса: как вывести эту самую позицию в профиле пользователя, а не в списке?

[Albert Kazan]

Rsa97, лол я сам допер до нужного запроса. Я обернул все это еще в один SELECT и указал условие WHERE на нужный id.

SET @row = 0;
SELECT * FROM (
    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`
 ) AS t3
 WHERE `id`=??

Это правильное решение или есть получше?

[Rsa97]

Albert Tatarin, Если работает, и по скорости устраивает, то можно и не трогать. Или уже производить денормализацию базы, добавляя счётчик прямо в таблицу `people` и триггерами меняя его при добавлении/изменении/удалении строк в таблице `votes`. Тогда запрос позиции будет примерно таким:

SELECT COUNT(*) + 1 AS `position` 
  FROM `people` AS `p1`
  JOIN `people` AS `p2` ON `p1`.`id` = :user_id
    AND (`p2`.`votes_count` > `p1`.`votes_count`
      OR (`p2`.`votes_count` = `p1`.`votes_count`
        AND `p2`.`name` < `p1.`name`))