SQL Query to get 10 hishest numbers in a field.

SupraKazza

I have a field with highscores but i am new to sql so i don't know how to make a query that selects the 10 highest highscores and gets them back, i also need them DESC. thanks in advance, any help is appreciated.

Derokorian

SELECT columns FROM table ORDER BY highscore DESC limit 10

SupraKazza

Im afraid it doesn't work I get 10 scores and names however they are not the highest 10 because the smallest score is in the table and they are not descending heres my code:

require_once "connect_to_royal.php";

$result = mysql_query("SELECT highscore FROM highscores_table ORDER BY highscore DESC limit 10") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Score</th> </tr>";

while($row = mysql_fetch_array( $result )) {

echo "<tr><td>"; 
echo $row['name'];
echo "</td><td>"; 
echo $row['highscore'];
echo "</td></tr>"; 
} 

echo "</table>";


mysql_close(); 


?>

Derokorian

According to the code you provided, $row['name']; is undefined because you are not selecting a name column in your query, only a highscore column. However there is no reason you should be getting incorrect scores, can you show data in your table and what you expect to get and what you are getting instead?

Note that this is working for me:

CREATE TABLE `scores` ( `player` varchar(16), `score` int(10) unsigned );
INSERT INTO `scores` (`player`,`score`) VALUES
('Dero',123),
('Dero',113),
('Dero',103),
('Dero',143),
('Brad',153),
('Brad',125),
('Brad',128),
('Brad',183),
('Weed',323),
('Weed',223),
('Weed',193),
('Weed',25);

SELECT `player`,`score` FROM `scores` ORDER BY `score` DESC LIMIT 5;

Returns:

player	score 
Weed	323
Weed	223
Weed	193
Brad		183
Brad		153

This is the expected result.

SupraKazza

Yeah in the real thing i have put a * to get both the fields just iw as testing something. this is whats in the table just 11 rows

                    1       Cheema	300
		2	Karan	730
		3	Muttu	640
		4	Saad	990
		5	Raj	        1400
		6	Balraj	780
		7	Zaki	        1780
		8	Josh	        1350
		9	Karan	23000
		10	Ethan	800
		11	Ting	       900

this is what i am getting: http://karanpatel.site90.com/constance_file.php

SupraKazza

couldn't even find the edit button^ but when i say table i mean there are 11 rows in the table in the database, thanks for the help.

Derokorian

You don't have to SELECT * to get both columns, just list them both, comma delimited. And looking at the output on the link you provided the answer becomes obvious to me; Why is you score column not of numeric type but rather a string? Look at the first character of each number, its organized descending by first character then second character etc, meaning this is stored as a string instead of numerically. you need to

ALTER TABLE highscores_table MODIFY highscore BIGINT NOT NULL

SupraKazza

thanks for helping me, it worked!

crowdfinch

Hi,

Wow! very nice information i get! Thanks a lot for enjoying this beauty article with me.

Web Design and Development