I am creating an application to run a fishing tournament (see http:// www.ruby-forum.com/topic/51209 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
Bill Pennington wrote:> I am creating an application to run a fishing tournament (see > http://www.ruby-forum.com/topic/51209 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 :-)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 Dell 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 descWell, 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 Dell wrote:> John Dell 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 > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >- Bill
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 Pennington wrote:> 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 Dell wrote: > >> John Dell 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 >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > > > - Bill > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >- Bill
Bill Pennington wrote:> 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 functionOk...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;
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 Dell wrote:> Bill Pennington wrote: >> 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 > > 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; > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >- Bill
Bill Pennington wrote:> Thanks for all your help John, I really appreciate it. That new querySure, 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(length*species.multiplier), 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; 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 Dell wrote:> Bill Pennington 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(length*species.multiplier), > 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; > > 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 > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >- Bill