GROUP BY and uniqueness in multiple columns

Question

Lets say that I am looking to select 3 columns from a few joined tables: event_id, home_team, and the results of an aggregate function. The event_id is a primary key autoincremented. The home_team is not unique to every record in the table. I want to group by event_id to generate the aggregate function and then sort by the reuslts of the aggregate funtion taking the highest 3 values. But I also want the home_team to be unique. How can I do this? I can't do DISTINCT because the record will have home_team, event_id, and results of aggregate function. The event_id will always be unique so my DiSTINCT query does nothing.

I have to group by event_id since the aggregate function depends on that so I am unable to group by home_team. If I group by both it won't help either because...

EXAMPLE

Table 1
  id: 1
  event_id: 1 
  value: 2

  id 2
  event_id: 1 
  value: 6

  id 3
  event_id: 2
  value: 4

  id4
  event_id: 2 
  value: 3

  id5
  event_id: 3
  value: 1

Table 2

event_id (PK, autoincremented): 1
home_team: 1

event_id: 2
home_team 2

event_id: 3
home_team: 1

SELECT *, AVG(table1.value) as average FROM table1 JOIN table2 ON table1.event_id = table2.event_id GROUP BY table2.event_id ORDER BY average DESC

I need to group by table2.event_id in order to generate the correct aggregate function result. However, I also want home_team to be distinct so the desired output will be two results with an event_id 1 and event_id 2

Answer 1

Use this:

GROUP BY event_id, home_team

This will group only those records which have the same event_id as well as the same home_team.

Answer 2

Well, an aggregate function grouped by a unique key is not going to do much for you, that's for sure. It sounds like you're getting mired in the idea of grouping when really all you want are the top three highest valued events for each home_team.

Try something like this, instead:

select
    event_id,
    home_team,
    value
from
    my_tbl t
where
    event_id in 
        (select event_id from my_tbl 
         where home_team = t.home_team 
         order by value desc limit 0,3)

Update: Your edit clears some things up, so here goes:

select
    (select min(event_id) from table2 where home_team = t2.home_team) as event_id,
    t2.home_team,
    avg(t1.value) as average
from
    table1 t1
    inner join table2 t2 on
        t1.event_id = t2.event_id
group by
    t2.home_team

What you're describing is fairly nonsensical. You want to group by event_id but then only show one home_team? Unless you're looking to take the top home_team row:

select
    home_team,
    max(average) as topavg
from
    (
    select
        t2.event_id,
        t2.home_team,
        avg(t1.value) as average
    from
        table1 t1
        inner join table2 t2 on
            t1.event_id = t2.event_id
    group by
        t2.event_id, t2.home_team
    ) as a
group by
    home_team