Is it possible to get the number of a specific row when sorting in MySQL


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
31 views
Example: I have N users with the Expo from 0 to M.
Users are sorted in descending order, from M to 0. Is it possible to get user id in this list?
Now I use REDIS for this.ZSET, but it would be interesting to know the solution for MySQL.
by | 31 views

4 Answers

0 like 0 dislike
--- karma user 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}); 

\r
The second condition in the OR need in case if two users have the same karma (sort then you have for exp and id to users with the same karma didn't jump from place to place even without a change in karma). If it can be neglected, and the condition can be neglected.
\r
But the truth is better to update specific field every 5 minutes a script.
by
0 like 0 dislike
SET @rn := 0;
\r
SELECT * FROM (
SELECT @rn := @rn+1 AS id, exp
FROM users
ORDER BY users DESC
) WHERE id = {n};
\r
something like this
by
0 like 0 dislike
SELECT u1.*, COUNT(u2.id)+1 AS rating FROM users u1, users u2 WHERE u1.id = ? AND u2.exp > u1.exp 

(The idea is to count the number of users with experience more than this)
by
0 like 0 dislike
SET @rows_count = NULL;
SELECT name, @rows_count := IFNULL(@rows_count, 0) + 1 FROM city LIMIT 10;
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
asked May 22, 2019 by atawerrus
0 like 0 dislike
1 answer
asked May 20, 2019 by ART_CORP
0 like 0 dislike
3 answers
110,608 questions
257,186 answers
0 comments
27,835 users