Rank for 1 User " You Are 995th at mysql in your town!"

[glendango]

i'am trying to get the users rank so i can display their rank in a div.. i.e you are 30 out of 400 users at getting sales. 

 

So far i ve managed to get the order of the users but thats it:     i count the date_mades as sales made to get the totals. 

 

SELECT usr_id, count(date_made) as dater, @curRank := @curRank + 1 AS rank FROM firsts 

group BY usr_id order by dater desc

 

 

gives me :

 

 

usr_id dater   rank    

   1        20      NULL

16            3     NULL

3             2        NULL

5            1         NULL

9             1        NULL

22          1         NULL

25          1       NULL

 

 

i found this answer (Attached snippet) but i just get red crosses all over the statement when i try and run it:  does anyone else get errors just pasting this into their phpmyadmin? is it a parser problem or syntax?  

 

 

 

 

SELECT pid, name, age, rank FROM

(SELECT pid, name, age,

@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,

@incRank := @incRank + 1,

@prevRank := age

FROM players p, (

SELECT @curRank :=0, @prevRank := NULL, @incRank := 1

) r

ORDER BY age) s

 

 

 

Edited April 17, 2018 by glendango

[requinix]

i found this answer (Attached snippet) but i just get red crosses all over the statement when i try and run it:  does anyone else get errors just pasting this into their phpmyadmin? is it a parser problem or syntax?

Are you saying that you found some SQL on the internet, copied and pasted it into phpMyAdmin, and expected it to work? Did you change the table and column names to match your actual schema?

[Barand]

Sort the data into correct sequence on X then read each record (where X is the column you are using to determine the rank).

 

As you read each record

• Maintain a record count .
• If current X = previous X the rank is same as previous rank value otherwise the rank is the current count

[glendango]

only for testing purposes..  i think this is the issue as i get same errors.   

 

 

https://github.com/phpmyadmin/phpmyadmin/issues/12080

[requinix]

Did you see that it was fixed in 4.6.5? Are you running something before that version? They're on 4.8.0 now...

[glendango]

And i use 4.6.4  ...typical.  where does it say its fixed?  just some thread that goes nowhere when i read it.  but thanks,,,do you think this is the issue?