GROUP BY and SORT BY causing issues

[widget]

Hi I am trying to return the results of a high scores table in php and mysql.

 

Sorting by score retrieves the following results.

 

joe 234

joe 213

jack 199

jill 198

jack 197

joe 145

 

I would like the results to sort by score and then group by username.

 

With my current code it shows the very first result in the database for the user no matter what the users score.

 

For example

 

Joe 213

Jill 198

Jack 197

 

Is there a way to do this?

 

$sql_query = "SELECT * FROM 'highscores_bubblepop' GROUP BY 'username' ORDER BY  'score' DESC LIMIT 0 , 50";
//store the SQL query in the result variable
$result = mysql_query($sql_query);

if(mysql_num_rows($result))
{
//output as long as there are still available fields
$x =1;

while($row = mysql_fetch_row($result))
{
$current_avatar = fetch("SELECT * FROM members_profiles2 WHERE username = '$row[1]'");
echo ("<div class=\"game_high_scores_content\" style=\"background-image: URL($base_url/images/avatars/$current_avatar[avatar].gif);\">$row[1] Scored $row[2]</div>");
$x ++;
}
}

//if no fields exist
else
{
echo "no values in the database";
} 
mysql_close($con);
?>

 

 

 

[WebStyles]

try something like: (assuming you want to add up each players scores ??)

"SELECT `username`,sum(`score`) as total FROM `highscores_bubblepop` GROUP BY `username` ORDER BY  SUM(`score`) DESC LIMIT 0 , 50"

[widget]

thanks for your response but no not wanting to add the scores.

 

Just display the highest score per user.

[WebStyles]

SELECT distinct `username`,`score` FROM `highscores_bubblepop` ORDER BY `score` DESC LIMIT 0 , 50

[widget]

I have tried using distinct but it didnt work, perhaps something else was wrong, I will try that again. thanks

 

will let you know

[WebStyles]

if you want to order by score and username, do exactly that: ORDER BY `score`,`username`

[widget]

Using that code gives me the below results

 

joe Scored 777

lollypopchicka Scored 269

lollypopchicka Scored 262

joe Scored 234

fred Scored 115

fred Scored 111

[WebStyles]

yes, cause it's ordered by score. if you also want to order by username see my previous post

[widget]

order by score and username give the below results

 

fred Scored 111

fred Scored 115

joe Scored 234

lollypopchicka Scored 262

lollypopchicka Scored 269

joe Scored 777

 

$sql_query = "SELECT distinct `username`,`score` FROM `highscores_bubblepop` ORDER BY `score`,`username` DESC LIMIT 0 , 50";

[WebStyles]

sorry, it's the other way round: order by `username`,`score`

[widget]

That kinda works

 

How would I then only include a players top score or 1 distinct entry?

 

Query

$sql_query = "SELECT distinct `username`,`score` FROM `highscores_bubblepop` ORDER BY `username`,`score` DESC LIMIT 0 , 50";

 

 

Results

 

fred Scored 115

fred Scored 111

joe Scored 777

joe Scored 234

lollypopchicka Scored 269

lollypopchicka Scored 262

 

On a personal note I just read your blog entry on selling your Mac, priceless!!

[WebStyles]

so you just want the highest score of each user... ('total' holds the score value)

SELECT username,MAX(score) as total FROM highscores_bubblepop group by username LIMIT 0 , 50"

 

On a personal note I just read your blog entry on selling your Mac, priceless!!

p.s. they guy was asking for it. lol

[widget]

ohheeeeeeee that kinda worked

 

fred Scored 115

joe Scored 777

lollypopchicka Scored 269

 

how can we display highest score 1st?

[widget]

woot nvm i added ORDER BY `score` DESC

 

$sql_query = "SELECT username,MAX(score) as total FROM highscores_bubblepop group by username ORDER BY `score` DESC LIMIT 0 , 50";

 

joe Scored 777

lollypopchicka Scored 269

fred Scored 115

 

I could kiss you!!! thank you soo much I have been trying to fix this problem for years on and off.

 

 

[WebStyles]

add order by MAX(score) DESC

[WebStyles]

you're quite welcome. Glad to have helped.

[widget]

Hi me again!

 

I now have beta testers on the site and it seems its not working as it should after all

 

$sql_query = "SELECT username,MAX(score) as total FROM highscores_bubblepop group by username ORDER BY `score` DESC LIMIT 0 , 50";


//store the SQL query in the result variable
$result = mysql_query($sql_query);

if(mysql_num_rows($result))
{
//output as long as there are still available fields
$x =1;

while($row = mysql_fetch_row($result))
{
$current_avatar = fetch("SELECT * FROM members_profiles2 WHERE username = '$row[1]'");
echo ("<div class=\"game_high_scores_content\" style=\"background-image: URL($base_url/images/avatars/$current_avatar[avatar].gif);\">$row[0] Scored $row[1]</div>");
$x ++;
}
}

//if no fields exist
else
{
echo "no values in the database";
} 
mysql_close($con);

 

Result

 

2random4me Scored 2097

lollypopchicka Scored 1541

loulou2370 Scored 603

plutoheart Scored 1662

maryann1950 Scored 2126

twinkle Scored 386

faeryofavalon Scored 632

[widget]

Is anyone able to shed some light on this?

[PFMaBiSmAd]

Is your score column an integer data type?

 

If you would post a .sql dump of your table,with the table definition and data, someone could try to duplicate the result to determine why it is not working.

[widget]

I worked it out

 

$sql_query = "SELECT username,MAX(score) as total FROM highscores_bubblepop group by username ORDER BY `total` DESC LIMIT 0 , 10";

 

Had to order by total not score and it now works perfectly.

 

Hopefully this helps others as I know its taken me a long long time to find the answer

 

Thank you all who helped