# I can't figure out how to solve this one

**URL:** <https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851>\
**Category:** Rails\
**Created:** [January 19, 2006, 7:09pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851 "2006-01-19T19:09:58Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 7:09pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/1 "2006-01-19T19:09:58Z")

</div>

I am creating an application to run a fishing tournament (see http://  
[Application Design Question - Rails - Ruby-Forum](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

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 7:34pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/2 "2006-01-19T19:34:08Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 7:43pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/3 "2006-01-19T19:43:10Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 8:43pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/4 "2006-01-19T20:43:32Z")

</div>

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](http://lists.rubyonrails.org/mailman/listinfo/rails)

- Bill

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 9:04pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/5 "2006-01-19T21:04:41Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 8:58pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/6 "2006-01-19T20:58:39Z")

</div>

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](http://lists.rubyonrails.org/mailman/listinfo/rails)

- Bill

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 19, 2006, 9:52pm UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/7 "2006-01-19T21:52:55Z")

</div>

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](http://lists.rubyonrails.org/mailman/listinfo/rails)

- Bill

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 20, 2006, 12:00am UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/8 "2006-01-20T00:00:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bplummer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/6de8d8/32.png) [@bplummer](https://www.ruby-forum.com/u/bplummer)\
**Post date:** [January 20, 2006, 12:39am UTC](https://www.ruby-forum.com/t/i-cant-figure-out-how-to-solve-this-one/52851/9 "2006-01-20T00:39:17Z")

</div>

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  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

- Bill