MySQL Broken Rank

Question

I have the following query which returns some event details, the number of votes and a rank.

SELECT e.guid, 
       e.name,
       (SELECT COUNT(ev.event_vote_id) 
        FROM event_vote sv 
        WHERE ev.event_uid = s.guid) AS votes,
       @curRank := @curRank + 1 AS rank
FROM event e, (SELECT @curRank := 0) r
ORDER BY votes DESC

It returns the correct details including votes but the rank is broken.

Actual Result

guid | name | votes | rank

def    test2     2      2
abc    test1     1      1 
ghi    test3     0      3
jkl    test4     0      4

Expected Result

guid | name | votes | rank

def    test2     2      1
abc    test1     1      2 
ghi    test3     0      3
jkl    test4     0      4

For some reason test1 has a higher rank than test2.

I assume I need to use a JOIN but i'm unsure on the syntax.

Juan Carlos Oropeza · Accepted Answer · 2018-02-28T15:26:06.030

2

You have to calculate the votes first, then calculate the ranking.

SELECT T.*, @curRank := @curRank + 1 AS rank
FROM ( SELECT e.guid, 
              e.name,
              (SELECT COUNT(ev.event_vote_id) 
               FROM event_vote sv 
               WHERE ev.event_uid = s.guid) AS votes
       FROM event e
    ) as T
CROSS JOIN  (SELECT @curRank := 0) r
ORDER BY votes DESC

You have wrong result because SELECT section occurs before ORDER section, so you already have a rank but not necessary match the order you get at the end.

Can read more about it here:

Order Of Execution of the SQL query

edited Feb 28 '18 at 15:26

answered Feb 28 '18 at 15:22

Juan Carlos Oropeza

47,252
12
78
118

I want to make this user specific by joining user_event. I added "JOIN user_event ue ON t.guid = ue.selfie_uid WHERE ue.user_uid = '33c2fb85'" after AS T but this gives me the opposite answer (events NOT related to the user). Any help would be appreciated. Thanks. – Shh Feb 28 '18 at 16:18
Is better if you create a new question. I cant understand your query because isnt formated. You can link this question as reference but you need include all relevant information on the new question. – Juan Carlos Oropeza Feb 28 '18 at 16:21

MySQL Broken Rank

1 Answers1