Dropping the lowest three integers 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

Dropping the lowest three integers in a results set?

Post 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?
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post 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.
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post 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!
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

Post by phpPete »

That's valid query syntax, so if that's not the actual query can you post the actual query?

---never mind..
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Re: Dropping the lowest three integers in a results set?

Post 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.
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

<waves hand>I didn't say this</wave>
Last edited by llimllib on Wed Aug 21, 2002 9:26 am, edited 1 time in total.
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post 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')
	&#123;
	$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)
		&#123;
		// drop worst three only if there have been more than three races 
		$query = "SELECT sum(points) FROM $series WHERE driver = '$driver&#1111;$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;
		&#125;	
	&#125;
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]' "; )
Last edited by Matt Phelps on Wed Aug 21, 2002 7:02 am, edited 1 time in total.
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post 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?!
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

I misinterpereted what you said. You were absolutely correct, and I humbly withdraw my statement. I apologize.
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Post 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')
		&#123;
		if ($num_races > 3)
			&#123;
				// drop worst three only if there have been more than three races 
				$query = "SELECT points FROM $series WHERE driver = '$driver&#1111;$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))
					&#123;
						$number = array_pop($cursor);
						$lowest_three_points = $lowest_three_points + array_pop($cursor);
					&#125;

				$total_points = $total_points - $lowest_three_points;
			&#125;	
		&#125;
Post Reply