I am creating an application to run a fishing tournament (see http://
Application Design Question - Rails - Ruby-Forum for a little background)
Now I am running into an issue trying to calculate a leader board.
each participate can enter multiple fish but only the largest fish
per a given species counts towards the overall score. My entries
table looks like this:
±------------±-------------±-----±----±--------------------
±---------------+
| Field | Type | Null | Key | Default |
Extra |
±------------±-------------±-----±----±--------------------
±---------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| species_id | int(11) | | | 0
| |
| location_id | int(11) | | | 0
| |
| caught_on | datetime | | | 0000-00-00 00:00:00
| |
| user_id | int(11) | | | 0
| |
| length | int(3) | | | 0
| |
| image | varchar(100) | YES | | NULL
| |
| created_at | datetime | YES | | NULL
| |
| updated_on | datetime | YES | | NULL
| |
±------------±-------------±-----±----±--------------------
±---------------+
The species table holds the multiplier for a given species so when I
want to see the Top 10 for a given species I can do this:
select entries.id, species.name, length,species.multiplier*length as
score,caught_on, (select login from users where id = entries.user_id)
as Angler, image from entries join species on species_id =
species.id where species_id = ? order by score DESC LIMIT 10
If users are only allowed to enter one fish per species then I can
just go count all there fish and it makes it simple. This is an
option FYI but not one I would like to use. However if users are
allowed to enter more than one fish per species then things get hard
(for me at least). I would like to use the MySQL MAX() function but
it appears to be a little to broad for this purpose. I can only get
the single biggest fish per species or per user not per user AND
species. I am pretty sure I could do this with a temp table but since
this is going to be hit all the time it seems silly to do that.
After typing all that I think I am realizing I should just limit them
to one entry per fish 
TIA!
Bill P. wrote:
| Field | Type | Null | Key | Default |
| |
±------------±-------------±-----±----±--------------------±---------------+
If users are only allowed to enter one fish per species then I can just
one entry per fish
SQL ‘group by’ is your friend. Aggregate functions (min, max, sum, etc)
work hand-in-hand with group by.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
How about:
select max(length) as length from entries group by species, user_id
order by species, length desc
John
John D. wrote:
SQL ‘group by’ is your friend. Aggregate functions (min, max, sum, etc)
work hand-in-hand with group by.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
How about:
select max(length) as length from entries group by species, user_id
order by species, length desc
Well, I can’t type…still untested (forgot columns in select):
select species, user_id, max(length) as length from entries group by
species, user_id order by species, length desc
John
Thanks John I tweaked the query a bit to this:
select species.name, user_id, max(length*species.multiplier) as score
from entries join species on species_id = species.id group by
species_id, user_id order by species_id, score desc;
Which returns:
±--------±--------±------+
| name | user_id | score |
±--------±--------±------+
| Cabezon | 3 | 182 |
| Salmon | 2 | 225 |
| Salmon | 3 | 140 |
| Salmon | 4 | 135 |
| Striper | 4 | 165 |
| Striper | 3 | 120 |
| Striper | 2 | 115 |
±--------±--------±------+
This is closer but I need to aggregate the users score now. I tried
to SUM(max(length*species.multiplier)) but I get a grouping error.
ERROR 1111 (HY000): Invalid use of group function
On Jan 19, 2006, at 10:02 AM, John D. wrote:
select max(length) as length from entries group by species, user_id
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
Bill P. wrote:
±--------±--------±------+
SUM(max(length*species.multiplier)) but I get a grouping error.
ERROR 1111 (HY000): Invalid use of group function
Ok…you really don’t want to use max at all. With the group by, the sum
will only sum the rows for the group by criteria (user and species).
So, this should give you what you want:
select
species.name,
user_id,
sum(length*species.multiplier) as score
from entries
join species on species_id = species.id
group by
species_id, user_id
order by
species_id, score desc;
Sorry just to follow up this query produces what I what but assumes
that there is only one of each type of species.
select (select users.login from users where users.id =
entries.user_id) as Angler, SUM(length*species.multiplier) as Score
from entries join species on entries.species_id = species.id group by
user_id order by Score DESC LIMIT 10;
±-------±------+
| Angler | Score |
±-------±------+
| Mooch | 442 |
| bill | 340 |
| Stuart | 300 |
±-------±------+
On Jan 19, 2006, at 11:04 AM, Bill P. wrote:
±--------±--------±------+
to SUM(max(length*species.multiplier)) but I get a grouping error.
sum, etc)
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
Thanks for all your help John, I really appreciate it. That new query
gives me the same results as the old query. I think I have to use MAX
() or something like it so I only retrieve the highest scored fish
per species. This query gives me exactly what I want but again
assumes the user is only able to enter one fish per species:
select (select users.login from users where users.id =
entries.user_id) as Angler, SUM(length*species.multiplier) as Score
from entries join species on entries.species_id = species.id group by
user_id order by Score DESC LIMIT 10;
±-------±------+
| Angler | Score |
±-------±------+
| Mooch | 442 |
| bill | 340 |
| Stuart | 300 |
±-------±------+
It is looking like overwriting (or just selecting the latest entry
per species) is the easiest way to go.
On Jan 19, 2006, at 11:24 AM, John D. wrote:
| name | user_id | score |
This is closer but I need to aggregate the users score now. I
select
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
Bill P. wrote:
Thanks for all your help John, I really appreciate it. That new query
Sure, although it sounds like this isn’t solved yet 
gives me the same results as the old query. I think I have to use MAX()
or something like it so I only retrieve the highest scored fish per
species. This query gives me exactly what I want but again assumes the
user is only able to enter one fish per species:
Hmm…If you want the leaderboard for the case where only 1 fish per
species is allowed, do the max. If you want the leaderboard for the
case where more than 1 fish per species is allowed do the sum.
So, lets say you add a flag column in the species table that says
one_fish_only = TRUE, then you can create an if statement in your select
that says if one_fish_only = 1, use the max, else use the sum.
select
species.name,
user_id,
IF(species.one_fish_only=1, max(lengthspecies.multiplier),
sum(lengthspecies.multiplier)) as score,
from entries
join species on species_id = species.id
group by
species_id, user_id
order by
species_id, score desc;
I didn’t try this so there is probably a syntax error, but I think
that would give you what you want
John
Ahh! of course I could just let the user choose what fish they want
to enter and just default it to the last one they add or write a
helper to check to see if it is bigger than the previous and flag it
accordingly. Then I can just add “is_entered = 1” to my where clause.
I think I see a little light…
Let me go try this, gives me a good excuse to write my first
migration as well
On Jan 19, 2006, at 2:22 PM, John D. wrote:
user is only able to enter one fish per species:
select
species_id, score desc;
I didn’t try this so there is probably a syntax error, but I think
that would give you what you want
John
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails