tsql - SQL Server MAX and GROUP BY not playing nicely together -

i have t-sql query of form:

select          f.fizz_name, b.buzz_version, fu.foo_name            fizz f inner join      buzz b on              f.fizz_id = b.fizz_id inner join      foo fu on              b.buzz_id = fu.buzz_id           f.bar 'yes' 

when run query following results:

fizz_name   buzz_version    foo_name ==================================== gamma       0.3.960         test gamma       0.3.961         test gamma       0.3.960         test gamma       0.3.961         test delta       0.3.2588        test delta       0.3.2589        test delta       0.3.2588        test delta       0.3.2589        test echo        2.2.38          test echo        2.2.38          test 

the problem contains lot of entries don't care about. in reality care largest buzz_version each fizz instance, in other words:

fizz_name   buzz_version    foo_name ==================================== gamma       0.3.961         test delta       0.3.2589        test echo        2.2.38          test 

...because "2.2.38" latest/lexiconographically-highest buzz_version echo, , same other fizzes.

so trying use group by in concert max fetch these values so:

select          f.fizz_name, max(b.buzz_version), fu.foo_name            fizz f inner join      buzz b on              f.fizz_id = b.fizz_id inner join      foo fu on              b.buzz_id = fu.buzz_id           f.bar 'yes' group        b.buzz_version 

but gives me error:

column 'fizz.fizz_name' invalid in select list because not contained in either aggregate function or group clause.

where going wrong, , why?

you grouping aggregate in query. need group scalar columns instead. in case, group f.fizz_name, fu.foo_name