Limit Groups by Number Using Transact-SQL or MS Access
You can easily limit or group a recordset, but doing both to the same recordset takes a bit of hand coding. Find out how both SQL Server and MS Access can handle the task.
by Susan Sales Harkins,
Francisco Tapia
May 19, 2009
imiting the number of records in a group is a common request. For example, suppose your sales director requests a custom report showing the top salesperson for each region. At first, limiting the number of records in that group seems simple: Use GROUP BY and TOP, but that doesn't work. Instead of limiting the number of records in each region, TOP retrieves just one record—whichever record happens to filter to the top of the recordset.
You might work your way through a number of other possibilities with varying degrees of success, including:
Combining GROUP BY and HAVING
Adding a COUNT() function to the mix
Using Transact SQL (T-SQL) to jump right to a subquery
The third solution goes in the right direction, but getting just the right syntax might be baffling. A subquery using IN is the way to go, unless you're using MS Access to report the SQL Server data. Then, you don't need code at all—the Report objects will limit the groups.
It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com. Already a member?
To become a member of DevX.com create your Member Profile by completing the form below. Membership is free!
imiting the number of records in a group is a common request. For example, suppose your sales director requests a custom report showing the top salesperson for each region. At first, limiting the number of records in that group seems simple: Use 
