I can't figure out how to solve this one


#1

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 :slight_smile:

TIA!

  • Bill

#2

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 :slight_smile:
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


#3

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


#4

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
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

#5

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;


#6

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)

  • Bill

Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

#7

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
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

#8

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 :slight_smile:

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(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 :slight_smile:

John


#9

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 :slight_smile:

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 :slight_smile:

John


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill