Jump to content

Rank in Highscores


Kryptix

Recommended Posts

OK, here's the issue -- the GROUP BY clause.  The counter makes no sense this way.

 

You have duplicate rows for the same user value -- why?

 

Eep! You are right. I was copying the query from one I use that has to sum several values and i forgot to remove it.

 

The corrected query should be:

 

SET @num :=0;

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
ORDER BY a.exp_attack DESC

 

Link to comment
Share on other sites

OK, here's the issue -- the GROUP BY clause.  The counter makes no sense this way.

 

You have duplicate rows for the same user value -- why?

 

The database corrupted a few months back and I haven't ever figured out how to delete the duplicates. I'd love to but not sure how. It doesn't seem to cause any problem but I guess it will in this query. Can I use DISTINCT() around `user` to prevent seeing the duplicates? If so, could you give me an example?

 

OK, here's the issue -- the GROUP BY clause.  The counter makes no sense this way.

 

You have duplicate rows for the same user value -- why?

 

Eep! You are right. I was copying the query from one I use that has to sum several values and i forgot to remove it.

 

The corrected query should be:

 

SET @num :=0;

SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank
FROM `rscd_experience` a
ORDER BY a.exp_attack DESC

 

Yay, there we go! We finally got there! Thank-you both for your efforts, you've been a massive help.

 

Now, this is the query I'm using:

 

SET @num :=0;

 

SELECT `rscd_players`.`user`, `username`, `exp_attack`, @num := @num +1 AS `rank`

FROM `rscd_experience`

JOIN `rscd_players` ON `rscd_players`.`user` = `rscd_experience`.`user`

WHERE `username` = 'Kryptix'

ORDER BY `exp_attack` DESC

 

That puts Kryptix as #1 rank even though he's not. How would I get around that? I basically need to enter a players `username` and then have it display the results like:

 

username | exp_attack | rank_attack | exp_strength | rank_strength | exp_defense | rank_defense

Kryptix    13049472    37            58472947      1              1305          3957

 

How would I achieve those results?

 

Link to comment
Share on other sites

The reason it is doing that is because the query is ranking your user ID after it is pulling the results. Essentially, it is seaching for your user ID then once it has that, it is applying the ranking.

 

In order to get an individual ranking you need to pull all users, rank them then pull just your user ID.

 

For that you would need to do this:

 

SET @num :=0;

 

SELECT a.username, a.user, a.Rank 
FROM (	SELECT b.username, a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank	
FROM `rscd_experience` a	JOIN `rscd_players` b ON `b`.`user` = a.`user`
GROUP BY a.user	ORDER BY Attack DESC	) AS a
WHERE a.username = 'Kryptix'

 

I think that will work. I don't have the rscd_players table to test it.

Link to comment
Share on other sites

  • 4 months later...

Sorry, did I not post the revised query?

Could you show me where please?

 

This seems to work OK but I need to set the var within the same query and I'm struggling with that:

 

SELECT a.username, a.user, a.Rank
FROM (
SELECT b.username, a.user, SUM(a.exp_attack) AS Attack, @num := @num +1 AS Rank   
   FROM `rscd_experience` a   JOIN `rscd_players` b ON `b`.`user` = a.`user`
   GROUP BY a.user   ORDER BY Attack DESC
) AS a
WHERE a.username = 'Kryptix'

 

The above query returns:

 

username | user        | Rank

Kryptix  | 29518873935 | NULL

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.