Basically how can I get around the GROUP BY rule of having column names in both SELECT and GROUP BY clauses for a MAX()?
I have a query that needs to display two key fields, a field that shows a MAX amount for each category, and also the 2 other supporting fields. My problems is getting the 3 supporting fields to show since GROUP BY only lets me display the aggregate field and the fields used to group. I think some type of JOIN might help but I can't get my head around it. Below is an example of my table.
Code:
Table:
NAME SESSION SCORE MINUTES COLOR
-------- ---------- ------ -------------- -------
Tom 1 30 10 Red
Bob 1 23 3 Blue
Bob 1 12 5 Red
Tom 2 1 2 Yellow
Bob 2 8 1 Red
Bob 2 16 3 Red
With Name & Session (together) being the key fields, SCORE being what I do MAX() on, and the other two as supporting data and I would like this output
Code:
NAME SESSION SCORE TIME COLOR
-------- ---------- -------- ------- --------
TOM 1 30 10 Red
Tom 2 1 2 Yellow
Bob 1 23 3 Blue
Bob 2 16 3 Red