>> ... but in my opinion, SQL server has a big flaw in requiring every field in the group by - because in JOINED selects, you do NOT have the case you are indicating, but something more like:

Let's take a simplier example:

City        State
----        -----
Cincinnati  Ohio
Cleveland   Ohio
Columbus    Ohio
Dayton      Ohio
New York    New York
Albany      New York

SELECT City, State
FROM the_table
GROUP BY State

What values would you expect for City? The first? The last? BTW, which is the first or the last?

By requiring every column in the column list to be in either the GROUP BY or an Aggregate function, SQL Server is capable of determining the value w/o "guessing".

-Mike