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

Ok, so the first major stumbling block I've run into seems to be a common problem. I'm making this post title the error message in case someone else is looking for the answer.

Basically - any time you want to get a list of columns from a table, but you want to group the results because your using sum() or max() or the like - SQL Server will not let you do this unless your grouping on every column - which doesn't really help because that would give you all the rows!

This problem arises often where you have a table that records things and has datestamps signifying when the event occured. Lets say you want to query a table and see only the MOST RECENT occurence of an event, well max(datestamp_col) would work great - now lets say you want to join this resultrow with another table to get some more meaningful info about the event - BAM - group isn't allowed

There are actually two solutions to the problem - the first is to get the max value from a subquery and return the value as a column - this is most elegant, but not exactly novice friendly or intuitive.

---- below is an example ----

SELECT rs.LastLogon, rs.[Name], lo.DC, lo.Location
FROM (
SELECT MAX(LastLogon) AS LastLogon, [Name] AS [Name]
FROM logons
GROUP BY [NAME]) rs
INNER JOIN logons lo
ON rs.[Name] = lo.[name]
AND rs.LastLogon = lo.LastLogon
ORDER BY rs.LastLogon DESC

------------------------------


The second solution works only for things like max() - don't aggregate and use "top 1" (yes this is like limit 1 in mySQL) instead and order by the date column or whatever instead. Hardly a universal solution though - so you're stuck with the first one in most cases.

Anyway, for those who have written extensive queries including columns not in the aggregation - have fun rewriting these!!