Page 1 of 1
Dropping the lowest three integers in a results set?
Posted: Tue Aug 20, 2002 8:23 pm
by Matt Phelps
I have the following snippit of code from my site and it doesn't work. I think I am trying to do something with MYSQL that it doesn't like:
Code: Select all
$query = "SELECT sum(points) FROM datatable WHERE driver = 'myname' ORDER BY points DESC LIMIT 3";
What I am trying to do is to find the lowest three numbers in the field 'points' and add them together. Can this be done using MYSQL or will I have to manipulate the data in php to find the answer?
Any suggestions?
Posted: Tue Aug 20, 2002 9:06 pm
by llimllib
Primary question: what error is it giving you?
Secondary stuff: Don't you want ascending numbers? If your list is {2,2,3,4,5,5} you want to add 2,2, and 3, which are the first 3 in ascending order.
Posted: Tue Aug 20, 2002 9:15 pm
by Matt Phelps
Well here's the thing - seems to be working now.
Well spotted though - I should have been ordering ASC not DESC. Thanks!
Posted: Tue Aug 20, 2002 9:22 pm
by phpPete
That's valid query syntax, so if that's not the actual query can you post the actual query?
---never mind..
Posted: Tue Aug 20, 2002 11:48 pm
by Matt Phelps
No, that's the query I'm using except I'm ordering ASC now rather than DESC. It does work. I think that the data in the table I was using was misleading me.
Re: Dropping the lowest three integers in a results set?
Posted: Wed Aug 21, 2002 5:33 am
by mikeq
Matt Phelps wrote:I have the following snippit of code from my site and it doesn't work. I think I am trying to do something with MYSQL that it doesn't like:
Code: Select all
$query = "SELECT sum(points) FROM datatable WHERE driver = 'myname' ORDER BY points DESC LIMIT 3";
What I am trying to do is to find the lowest three numbers in the field 'points' and add them together. Can this be done using MYSQL or will I have to manipulate the data in php to find the answer?
Any suggestions?
Surely this query would only return 1 record, you are doing a SUM on a field based on only 1 driver, so it is going to sum all points for that 1 driver, there is no other grouping involved.
Posted: Wed Aug 21, 2002 6:26 am
by llimllib
<waves hand>I didn't say this</wave>
Posted: Wed Aug 21, 2002 6:45 am
by Matt Phelps
I simplified the code before I posted it here - maybe I should post the full snippit to show you exactly how it's supposed to work:
Code: Select all
if ($drop_worst_three == 'yes')
{
$query = "SELECT driver from $series WHERE position = '1' ";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$num_races = mysql_num_rows($result);
if ($num_races > 3)
{
// drop worst three only if there have been more than three races
$query = "SELECT sum(points) FROM $series WHERE driver = '$driverї$counter]' ORDER BY points ASC LIMIT 3";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$lowest_three_points = mysql_result($result, 0);
$total_points = $total_points - $lowest_three_points;
}
}
The idea is that for each driver in the array $driver the code checks through the table called $series and finds all the entries for that driver - and specifically finds all his points.
Say the driver is me and I entered five races and scored 10,15, 40, 24 and 5 points then I want the code to work out that 5, 10 and 15 were my worst results and subtract them from my $total_points (already calculated elsewhere using
$query = "SELECT sum(points) FROM $series WHERE driver = '$driver[$counter]' "; )
Posted: Wed Aug 21, 2002 6:53 am
by Matt Phelps
Ah no...hold on it doesn't work does it. Got fooled by the data again.

This just makes the $total_points = zero no matter what.
Damn! How can I do this?!
Posted: Wed Aug 21, 2002 8:52 am
by mikeq
llimllib wrote:There could be several references to myname in that one table - imagine that it's a foreign key, and there are 3 cars in this table witih 'myname' as a driver. You're making the assumption that myname is the primary key for the table.
Hi llimllib,
I'm making no assumptions, it doesn't matter if it isn't the primary key in fact it can't be the primary key because it is not unique, that query
will only return 1 record, try it for yourself.
If you have
CAR|DRIVER|POINTS
1 |Mike |10
2 |Mike |5
1 |llimllib |10
2 |llimllib |20
1 |Mike |15
2 |llimllib |2
3 |llimllib |5
select SUM(POINTS) Total
from datatable
where driver = 'Mike'
You would get 1 record with
Total
30
Because there are no other fields in the query to produce a grouping other than by the driver name you will always only get 1 record, Fact not assumption
However, if you do
select CAR, SUM(POINTS) Total
from datatable
where driver = 'Mike'
GROUP BY CAR
You would get more than 1 record with
CAR|TOTAL
1 |25
2 |5
If anyone gets any different answers I would love to see the SQL and the table data.
Posted: Wed Aug 21, 2002 9:25 am
by llimllib
I misinterpereted what you said. You were absolutely correct, and I humbly withdraw my statement. I apologize.
Posted: Wed Aug 21, 2002 11:05 am
by Matt Phelps
In case someone comes along and is looking for a similar solution to the problem in the future (well you never know!) then here is how I achieved my goal:
Code: Select all
if ($drop_worst_three == 'yes')
{
if ($num_races > 3)
{
// drop worst three only if there have been more than three races
$query = "SELECT points FROM $series WHERE driver = '$driverї$counter]' ORDER BY points ASC LIMIT 3";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$lowest_three_points = '0';
while ($cursor = mysql_fetch_array($result))
{
$number = array_pop($cursor);
$lowest_three_points = $lowest_three_points + array_pop($cursor);
}
$total_points = $total_points - $lowest_three_points;
}
}