Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
speedamp
Forum Commoner
Posts: 45 Joined: Tue Apr 29, 2003 3:59 pm
Post
by speedamp » Tue Nov 09, 2004 12:02 pm
hello everybody.....here is my dilemna:
each entrant to a database has 10+ scores, and i need to add the 7 highest (as total).
Can I do this? I'm close i think, but i can't get this valid. Should I use SUM, COUNT, CONCAT, etc to return ONLY the top 7 values?
Code: Select all
SELECT one_points, two_points, three_points, four_points, five_points, six_points, seven_points, eight_points
FROM table
WHERE sex = 'male'
ORDER BY total ASC
LIMIT 7
Thanks!
-Michael
Weirdan | Help us, help you. Please use Code: Select all
tags where approriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Tue Nov 09, 2004 12:08 pm
perhaps something like this:
Code: Select all
SELECT name, one_points+two_points+three_points+four_points+five_points+six_points+seven_points+eight_points as total
FROM table
WHERE sex = 'male'
ORDER BY total DESC
LIMIT 7
This query selects names of those males who has highest sum of eight columns.
speedamp
Forum Commoner
Posts: 45 Joined: Tue Apr 29, 2003 3:59 pm
Post
by speedamp » Tue Nov 09, 2004 12:13 pm
no, that won't do it..... i don't want the ranking among the entrants.
Each entrants total needs to be the highest 7 of his scores.
For example, each person has 10 point values.....i need to total that top 7, and assign that number as "total"
-mike
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Tue Nov 09, 2004 12:23 pm
do you need to get the highest scores ever? Like:
Code: Select all
Max scores ever:
one_points: 123
two_points: 345
three_points: 234
etc....
?
swdev
Forum Commoner
Posts: 59 Joined: Mon Oct 25, 2004 8:04 am
Post
by swdev » Tue Nov 09, 2004 12:38 pm
Not following you here,
Post the table definition, some sample data and the results you want to see.
speedamp
Forum Commoner
Posts: 45 Joined: Tue Apr 29, 2003 3:59 pm
Post
by speedamp » Tue Nov 09, 2004 12:50 pm
actually the concept isn't that tough, but I think i'm explaining it strangely.
Here is an example:
Code: Select all
field_name value
---------------- ---------------
name: Bill Rodgers
one_points 100
two_points 200
three_points 29
four_point 0
five_points 700
six_points 1101
seven_points 26
eight_points 100
nine_points 4
ten_points 224
What I need to do is calculate the TOTAL of the 7 highest values from this structure.
Does that make more sense?
-mike
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Tue Nov 09, 2004 1:42 pm
speedamp wrote:
Does that make more sense?
That does, but most RDBMS aren't designed for such tasks
Hence you need to do that in your client application, something like this:
Code: Select all
//... connect to the db
$rs = mysql_query('select * from table');
$res = array();
while($q = mysql_fetch_row($rs)) $res[] = $q;
foreach($res as $row)
$name = array_shift($row);
rsort($row); // sort in reverse order
$total = array_sum(array_slice($row, 0, 7)); // sum up 7 highest scores
echo $name . ' has achieved the total score of ' . $total . ' points';
}
speedamp
Forum Commoner
Posts: 45 Joined: Tue Apr 29, 2003 3:59 pm
Post
by speedamp » Mon Nov 29, 2004 2:39 pm
I rewrote this a bit, and wanted to know how to limit the 7 highest POINT totals into a seperate value?
Code: Select all
<?php
$result = mysql_query('SELECT * from table');
while ($row = mysql_fetch_assoc($result))
{
foreach ($row as $key => $value)
{
if (strstr($key, '_points')) {$points[] = $value;}
}
$sum_array[$row['table_id']] = array_sum($points);
$data_array[$row['table_id']] = $row;
}
arsort($sum_array);
echo 'the total combined scores are:<br><br>';
foreach ($sum_array as $key => $value)
{
echo '
ID: ' . $data_array[$key]['table_id'] . '<br>
first name: ' . $data_array[$key]['first_name'] . '<br>
last name: ' . $data_array[$key]['last_name'] . '<br>
total score: ' . $value . '<br><br>
';
}
?>
where do i put the limit?
thanks,
-mike