CODEWITHSUNDEEP
sql-servert-sqlgroup-bymaxaggregate-functions
smeeb
smeeb

Reputation: 29537

SQL Server MAX and GROUP BY not playing nicely together

I have a T-SQL query of the form:

SELECT          f.fizz_name, b.buzz_version, fu.foo_name
FROM            fizz f
INNER JOIN      buzz b
ON              f.fizz_id = b.fizz_id
INNER JOIN      foo fu
ON              b.buzz_id = fu.buzz_id
WHERE           f.bar LIKE 'YES'

When I run this query I get the 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 with this is that it contains a lot of entries that I don't care about. In reality I only care about the largest buzz_version for 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" is the latest/lexiconographically-highest buzz_version for Echo, and same for the other fizzes.

So I am trying to use GROUP BY in concert with MAX to fetch these values like so:

SELECT          f.fizz_name, MAX(b.buzz_version), fu.foo_name
FROM            fizz f
INNER JOIN      buzz b
ON              f.fizz_id = b.fizz_id
INNER JOIN      foo fu
ON              b.buzz_id = fu.buzz_id
WHERE           f.bar LIKE 'YES'
GROUP BY        b.buzz_version

But that gives me an error:

Column 'fizz.fizz_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Where am I going wrong, and why?

Upvotes: 0

Views: 91

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

You are grouping by the aggregate in your query. You need to group by the scalar columns instead. In this case, group by f.fizz_name, fu.foo_name

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94959

You want one result row per fizz_name, so you must group by fizz_name. You show MAX(b.buzz_version) with it and must decide which fu.foo_name to show. E.g.:

SELECT f.fizz_name, MAX(b.buzz_version), MAX(fu.foo_name)
FROM fizz f
INNER JOIN buzz b ON f.fizz_id = b.fizz_id
INNER JOIN foo fu ON b.buzz_id = fu.buzz_id
WHERE f.bar LIKE 'YES'
GROUP BY f.fizz_name;

Upvotes: 1

Related Questions