Можно ли получить номер определенной строки при сортировке в MySQL

Question

[Azy]

Пример: у меня есть N пользователей с экспой от 0 до M.
Пользователи отсортированы по убыванию, от M до 0. Можно ли получить номер пользователя с id в этом списке?
Сейчас использую для этого REDIS.ZSET, но было бы интересно узнать решение на MySQL.

Answer 1

[middle]

--- карма пользователя n.
SET @user_exp = (SELECT exp FROM users where id = {n});

SELECT count(*) FROM users
    WHERE exp > @user_exp OR (exp = @user_exp AND user.id < {n});

Второе условие в OR нужно на тот случай, если у двух пользователей окажется одинаковая карма (сортировать тогда нужно и по exp, и по id, чтобы пользователи с одинаковой кармой не прыгали с места на место даже без изменения кармы). Если этим можно пренебречь, то и условием можно пренебречь.

Но лучше правда обновлять специальное поле каждые 5 минут скриптом.

Comments

[Azy]

Вот, отличное решение. Только вместо OR я бы сделал order_by ID — это бы решило проблемы с «прыганием»

[Azy]

Но лучше правда обновлять специальное поле каждые 5 минут скриптом.

Ну или кешировать значение )

[middle]

order by не влияет на count(*) ;)

Answer 2

[phasma]

SET @rn := 0;

SELECT * FROM (
SELECT @rn := @rn+1 AS id, exp
FROM users
ORDER BY users DESC
) WHERE id = {n};

что-то вроде этого

Comments

[phasma]

s/@rn := @rn+1 AS/@rn := @rn+1 AS rownumber,

[Azy]

вложенные селекты довольно сильно жрут память

[Azy]

но решение интересное, спасибо

[phasma]

если это какой-то рейтинг, то не лучше хранить отдельным полем это или в отдельной таблице. Обновлять раз в 5 минут скриптом.

Answer 3

[apangin]

SELECT u1.*, COUNT(u2.id)+1 AS rating FROM users u1, users u2
WHERE u1.id = ? AND u2.exp > u1.exp

(Идея в том, чтобы считать кол-во пользователей с experience больше, чем у данного)

Comments

[apangin]

Кстати, еще бонус этого варианта в том, что у пользователей с одинаковой экспой будет одинаковый рейтинг.

[Azy]

А какое количество ячеек будет использоваться в выборке? 1 из u1 и N (где N — кол-во пользователей с кармой < u1.exp)?

[apangin]

Да. Эффективно это то же самое, что предложил middle, только одним запросом вместо двух.

Answer 4

[XuMiX]

SET @rows_count = NULL;
SELECT name, @rows_count := IFNULL(@rows_count, 0) + 1 FROM city LIMIT 10;