Jump to content
glendango

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

Recommended Posts

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 dot.gif  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 by glendango

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.