Need help with pulling TOP DISTINCT rows

[David R]

I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.

These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.

queryDoTheseFirst:

SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity], 
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN 
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID]) 
ON [Priority List].Activity = ImportTemp.[Inspection Activity]
WHERE ((([Activity Created]+[AddDays])<Now()))
ORDER BY [Priority List].Priority, queryNeededFirst.DPDMax DESC, 
Int(Now()-[Activity Created]-[AddDays]) DESC;

subquery (queryNeededFirst):

SELECT TOP 12 ImportTemp.[Inspection Activity], ImportTemp.[SO ID], 
Int(Now()-[Activity Created]-[AddDays]) AS DPDMax
FROM [Priority List] INNER JOIN ImportTemp ON 
[Priority List].Activity = ImportTemp.[Inspection Activity]
WHERE ((((Int(Now()-[Activity Created]-[AddDays]))>0))
ORDER BY [Priority List].Priority, 
Int(Now()-[Activity Created]-[AddDays]) DESC;

The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.

I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount). Other ideas??

 

[jdraw]

Since you have 1800+ posts, I take it that you are familiar with
Allen Browne's Top N.

That's the reference that seems most popular.

 

[David R]

Nevermind... writing the question out answered it for myself. Changing the subquery from TOP 15 to TOP 12 and removing the TOP requirement entirely in the final query did what I needed!