How to find the lowest 3 values in a results set?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

How to find the lowest 3 values in a results set?

Post by Matt Phelps »

I have a MYSQL database like this:

Code: Select all

NAME||POINTS
==========
Matt ||45
==========
Dave ||13
==========
Matt || 23
==========
Steve|| 14
==========

etc
I want to pull out all the data in the field POINTS where the NAME field is the same (SELECT points FROM results WHERE name = 'Matt' ) and then I want to find some way to work out the three lowest values in the results set and discard them and then add the remaining values together and enter them in another table.

The bit I'm having some trouble working out how to do is how to pull out the results and work out the lowest three values. I was wondering if I could use the min(arg1,arg2,arg3...) function to do this with a loop that repeats three times so that every time I go through the loop I would extract the lowest value but how do I get the data out of the database and into the arg1, arg2 etc values? Is there some sort of array command or something I could use?

Any help that anyone can offer would be great!
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

try a query like

Code: Select all

SELECT * FROM table ORDER BY points DESC LIMIT (0, 3)
and then do what you want with them, i dont quite understand if you want them printed into an html table.. which would be like:

Code: Select all

<?

/* connection */

$qry = mysql_query("SELECT * FROM table ORDER BY points DESC LIMIT (0, 3)");

echo '<table><tr><td>Name:</td><td>Points:</td></tr>';

while($data = mysql_fetch_assoc($qry))&#123;
echo '<tr><td>'.$data&#1111;'name'].'</td><td>'.$data&#1111;'points'].'</td></tr>';
&#125;

echo '</table>';

?>
or, switch them into a new table

Code: Select all

<?

/* connection */

$qry = mysql_query("SELECT * FROM table ORDER BY points DESC LIMIT (0, 3)");


/* empty out the new table */
mysql_query("DELETE * FROM new_table");
while($data = mysql_fetch_assoc($qry))&#123;
$name = $data&#1111;'name'];
$points = $data&#1111;'points'];
/* insert new record */
mysql_query("INSERT INTO new_table ('name','points') values ('$name', '$points')");
&#125;
?>
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post by Matt Phelps »

Aha! Of course! If I just limit my query and order it right then I can get the lowest numbers! Blindingly obvious now you mention it! Thanks. :)
Post Reply