Dirk Bergmann

Developer, panographer. Does consultancy work from own company. Lives in Chiang Mai, Thailand.

Twitter

Chiang Mai

Hua Hin

Thailand Hotels

ListVote - List. Vote. Learn.

Partnerseek - Find Partners

Combining TOP and GROUP BY with SQL Server 2005

It's an every day task: Select the ten top-selling widgets from each category. The obvious approach is to combine TOP and GROUP BY, which usually comes with some annoying "Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" errors you try to work around. The culprit is a misunderstanding of the GROUP BY clause. It will NOT give you multiple result sets, but rather ONE result set with a single row for each group. From Books Online:

GROUP BY produces a single result set. There is one row for each group containing only the grouping columns and aggregate functions that show the subaggregate for that group. The select list can contain only the grouping columns and aggregate functions.

Here's how to achieve the desired "TOP X PER GROUP" results using a variety of features introduced with SQL Server 2005.