Top 5

[Ray]

I have a query that groups failures by audit type. I want it to group only
the top 5 failures for each audit type. (not just the top 5 failures)

Suggestions?

SELECT spdMainQuery.AuditType, spdFailures.Defect, Count(spdFailures.Defect)
AS Occur, Sum(spdFailures.ItemizedFailedSamples) AS [Sum]
FROM spdMainQuery LEFT JOIN spdFailures ON spdMainQuery.MainPriKey =
spdFailures.MainPriKey
GROUP BY spdMainQuery.AuditType, spdFailures.Defect
HAVING (((spdFailures.Defect) Is Not Null))
ORDER BY spdMainQuery.AuditType, Count(spdFailures.Defect) DESC;

 

[Allen Browne]

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[Ray]

Thank you very much

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that groups failures by audit type. I want it to group only
the top 5 failures for each audit type. (not just the top 5 failures)

Suggestions?

SELECT spdMainQuery.AuditType, spdFailures.Defect,
Count(spdFailures.Defect)
AS Occur, Sum(spdFailures.ItemizedFailedSamples) AS [Sum]
FROM spdMainQuery LEFT JOIN spdFailures ON spdMainQuery.MainPriKey =
spdFailures.MainPriKey
GROUP BY spdMainQuery.AuditType, spdFailures.Defect
HAVING (((spdFailures.Defect) Is Not Null))
ORDER BY spdMainQuery.AuditType, Count(spdFailures.Defect) DESC;

 

[Franklin Smith]

Thanks Allen for furnishing this solution. I just made use of it to achieve
a very similar result, in my organization's fundraising database (8 most
recent donations per donor...)

Regards,

Franklin

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that groups failures by audit type. I want it to group only
the top 5 failures for each audit type. (not just the top 5 failures)

Suggestions?

SELECT spdMainQuery.AuditType, spdFailures.Defect,
Count(spdFailures.Defect)
AS Occur, Sum(spdFailures.ItemizedFailedSamples) AS [Sum]
FROM spdMainQuery LEFT JOIN spdFailures ON spdMainQuery.MainPriKey =
spdFailures.MainPriKey
GROUP BY spdMainQuery.AuditType, spdFailures.Defect
HAVING (((spdFailures.Defect) Is Not Null))
ORDER BY spdMainQuery.AuditType, Count(spdFailures.Defect) DESC;