# The Best of Baseball Awards - Returning Only Max/Min Values

Hi everyone!

Currently working on the ‘The Best of Baseball Awards’ Off Platform Project.

In the first 2 questions in Part 3 (‘Heaviest Hitters’ and ‘Shortest Sluggers’), the solutions provided a table that lists out heaviest players, grouped by teams and year, in no particular order:

Is there a way to use perhaps Windows Functions to only return the maximum average weight (for the first question, for example), per team, per year? I tried to use `MAX(AVG(people.weight))`, but it is not allowed in Postbird.

Did you check out the hint in the instructions?

Try using `GROUP BY`, `AVG` and then use `ORDER BY DESC`

Hi Lisa, that hint provided the above table (screenshotted in my original post), but did not provide, for every year, the maximum avg weight, and name of the team. In the way I am asking, there should only be one yearid, maybe two or three if they are tied 1st.

Is there a way to use a Windows Function to do this?

Yes, I believe you could do something like (I don’t know the column names in the project b/c I’ve not done it):

``````SELECT weight, team, yearid,
AVERAGE(weight) OVER( PARTITION BY team ORDER BY yearid) AS Avg_Weight

FROM Table_Name
``````

What would you write for this?

Hi Lisa, I wrote the following:

``````-- HEAVIEST HITTERS
SELECT
AVG(people.weight) OVER (
PARTITION BY teams.name
ORDER BY batting.yearid
) AS avg_weight,
teams.name,
batting.yearid
FROM people
INNER JOIN batting
ON people.playerid = batting.playerid
INNER JOIN teams
ON batting.team_id = teams.id
GROUP BY 2, 3
ORDER BY
AVG(people.weight)
DESC;
``````

and got the following error:

column “people.weight” must appear in the GROUP BY clause or be used in an aggregate function

Stack Overflow has noted that this is because I need to use a subquery instead. How would I go about doing that?

Hello @henrylin03 ,

Can you try adding the column alias 1 to the `GROUP BY` clause?

1 Like