CODEWITHSUNDEEP
mysql
hodl
hodl

Reputation: 1420

MySQL ranking query

Query:

SELECT u.user_id,r.totalVotes votes,r.totalPoints rating,@row:=@row+1 rank 
FROM mismatch_user u 
LEFT JOIN ratingItems r ON u.user_id=r.uniqueName,
(SELECT @row:=0) pos 
ORDER BY votes DESC,rating DESC

Output:

user_id    votes   rating  rank
2            2      10      2
6            2      9       6
3            2      5       3
1            1      5       1
4            1      5       4
27           1      5       27
9            0      0       9

The ranking miserably not telling me the truth and it's basing on the user_id. Can anyone help me?

Upvotes: 2

Views: 2819

Answers (2)

Henrique Ordine
Henrique Ordine

Reputation: 3337

Does this help?

select r.*, @row:=@row+1 rank 
  from 
    (SELECT u.user_id,r.totalVotes votes,r.totalPoints rating
       FROM mismatch_user u 
         LEFT JOIN ratingItems r ON u.user_id=r.uniqueName
    ) r
    join (SELECT @row:=0) pos 
ORDER BY r.votes DESC, r.rating DESC

Upvotes: 4

Olivier Coilland
Olivier Coilland

Reputation: 3096

You're generating a sequence and that can lead to tricky behavior.

If your query is correct, the safest way to sort by rank is now to embed it in another SELECT:

SELECT *
FROM (
    SELECT u.user_id,r.totalVotes votes,r.totalPoints rating,@row:=@row+1 rank 
    FROM mismatch_user u 
    LEFT JOIN ratingItems r ON u.user_id=r.uniqueName,
    (SELECT @row:=0) pos 
    ORDER BY votes DESC,rating DESC) T
ORDER BY rank;

Upvotes: 1

Related Questions