Rank in Highscores

[josborne]

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

 

[Kryptix]

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?

 

[josborne]

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.

[fenway]

And then you could still initialize the user variable in the query as I suggested earlier.

[Kryptix]

I still can't get it working the way I want. Could I possible upload `rscd_players` too so you can double check? It's just not right.

[fenway]

Sorry, did I not post the revised query?

[Kryptix]

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

[Kryptix]

Anyone?

[awjudd]

I provided you with a solution over MSN (we both saw it worked on my local server) ... then you disappeared ...

 

~juddster

[Kryptix]

Accidentally bumped this. I meant to edit the previous post saying what's been done, heh.