how to select top N in access report detail section or select top N in sql subquery
I have a report that is based on a query. In the detail section of the report I want to limit the number of det开发者_开发百科ail lines to top N. (like a select TOP N type query). In this sample I want the top 2 rows. Query I have so far is
SELECT TestTable.[GroupByCol], TestTable.[DetailColA]
FROM TestTable group by TestTable.[GroupByCol], TestTable.[DetailColA]
I know I need to join and do some having and count but it's just not clicking. I have also attached a screenshot.
Please advise. thank you
Try the following query as your report recordsource:
SELECT TestTable.GroupByCol, TestTable.DetailColA
FROM TestTable
WHERE TestTable.DetailColA IN
(SELECT TOP 2 DetailColA FROM TestTable AS TT
WHERE TT.GroupByCol=TestTable.GroupByCol
ORDER BY TT.DetailColA)
ORDER BY TestTable.GroupByCol, TestTable.DetailColA
See Allen Browne's Subquery Basics for more information.
EDIT: As you alluded to in your comment, this can also be written as follows:
SELECT a.GroupByCol, a.DetailColA, COUNT(*) As RankNumber
FROM TestTable AS a INNER JOIN TestTable AS b
ON a.GroupByCol = b.GroupByCol
AND a.DetailColA >= b.DetailColA
GROUP BY a.GroupByCol, a.DetailColA
HAVING COUNT(*)<=2
ORDER BY a.GroupByCol, a.DetailColA, COUNT(*)
You would have to benchmark to be sure, but this second version is probably more efficient. Which is more readable is probably a matter of personal preference (I find the first version more intuitive and readable).
精彩评论