MAX() Aggregate Function with Multiple Columns

[ebs57]

Can someone please enlighten me as to how I can select the maximum value from a table with multiple columns in the SELECT line?

What I mean is, if I use this code:

SELECT Max(Revision)
FROM Documents;

...everything is fine, but if I use this code:

SELECT Max(Revision), Title
FROM Documents;

...frogs and hail. The error message is "You tried to execute a query that does not include the specified expression 'Title' as part of an aggregate function." Any help appreciated.

[MMcCarthy]

This should work

SELECT Max(Revision), Title
FROM Documents
GROUP BY Title;

[ebs57]

Ms. McCarthy,

Would you please explain why GROUP BY will appease the angry MAX() command gods? I am trying to get a handle on SQL and VBA and -- although I certainly appreciate the fix you've offered -- I would definitely like to understand the reasoning behind IBM's or whomever's reasoning for requiring MAX() all the columns in the SELECT line.

My impression from the error message was the SQL statement would not evaluate because it somehow wanted to apply MAX() to all columns in the selected table. Apparently, GROUP BY takes care of this??? Please use small words in your explanation.

- Lumpy

[MMcCarthy]

Hi Lumpy

Think of it as a Logical statement. This give the Maximum Revision per Title. Select Maximum Revision for each Title in table Documents.

Without the Group By it would be trying to return the max value on each record, in other words every record.

When using aggregates (Sum, Avg, Count, Max, etc.) in a query you must always group by any field not aggregated.

If for example you had included an Revised By field in your query, that would also have to be included in the Group By.

SELECT Max(Revision), Title, RevisedBy
FROM Documents
GROUP BY Title, RevisedBy;

The results this time would be a Max Revision per Title per RevisedBy. So If a Title was revised by two people it would give the max revision for each.

Mary

[ebs57]

Holy Witchetty Grub -- I think I gets it!

Your opening statements really clarified it for me: "Select the Maximum Revision for each Title in table Documents." So, the other side of GROUP BY is to exclude columns from calculations.

Tanks...