CODEWITHSUNDEEP
mysql
El Tito Barte
El Tito Barte

Reputation: 275

Get sigle user ranking position from MySQL 5.7

I'm trying to get ranking position of single user. I already have the query to get the global ranking but not a single user.

I try:

SELECT FIND_IN_SET(`points`, (SELECT GROUP_CONCAT(`points` ORDER BY `points` DESC) FROM `users`)) AS `rank` FROM `users` WHERE `user` = '1'

But this query return repeated rank position if many users have same points.

My global rank query is:

SET @rank := 0; SELECT `user`, `points`, @rank := @rank + 1 AS `rank` FROM `users` ORDER BY `points` DESC, `user` ASC

But in this case I can't get single user position in rank. Any idea how I can get single position rank without ranking number repeated?

My MySQL version is 5.7 so can't use funcions like RANK() OVER(). Thanks.

Upvotes: 0

Views: 181

Answers (1)

forpas
forpas

Reputation: 164069

In your 1st query, concatenate user and points and use it in GROUP_CONCAT() where you should also extend the ORDER BY clause to include user as a tie breaker:

SELECT FIND_IN_SET(
         CONCAT(`user`, '|', `points`), 
         (
          SELECT GROUP_CONCAT(CONCAT(`user`, '|', `points`) ORDER BY `points` DESC, `user`) 
          FROM `users`
         )
       ) AS `rank` 
FROM `users` 
WHERE `user` = ?

Replace ? with the user that you want.

See the demo.

Upvotes: 1

Related Questions