Top 3 per group

[Eric Brenner]

I do not know how to get the Top 3 items by group and I can't seem to
find anywhere the breaks down an example well. Anyway, I want to take
this Access 97 query:

SELECT CaseAttribute.ProductLineCode, Attribute.Name,
Sum(CaseAttribute.Deductions) AS SumOfDeductions
FROM Attribute INNER JOIN (CaseAttribute INNER JOIN Audit ON
CaseAttribute.AuditID = Audit.ID) ON (Attribute.ProductLineCode =
CaseAttribute.ProductLineCode) AND (Attribute.ID =
CaseAttribute.AttributeID)
WHERE (((Audit.AuditDate) Between #1/1/2012# And #12/31/2012#))
GROUP BY CaseAttribute.ProductLineCode, Attribute.Name
ORDER BY CaseAttribute.ProductLineCode, Sum(CaseAttribute.Deductions)
DESC;

....and get the Top 3 items by CaseAttribute.ProductLineCode then by
Attribute.Name. I've searched many sites but I can't seem to get the
concept down. Anyone care to help?

Thanks.

 

[Bob Barrows]

I do not know how to get the Top 3 items by group and I can't seem to
find anywhere the breaks down an example well. Anyway, I want to take
this Access 97 query:

SELECT CaseAttribute.ProductLineCode, Attribute.Name AS AttributeName,
Sum(CaseAttribute.Deductions) AS SumOfDeductions
FROM Attribute INNER JOIN (CaseAttribute INNER JOIN Audit ON
CaseAttribute.AuditID = Audit.ID) ON (Attribute.ProductLineCode =
CaseAttribute.ProductLineCode) AND (Attribute.ID =
CaseAttribute.AttributeID)
WHERE (((Audit.AuditDate) Between #1/1/2012# And #12/31/2012#))
GROUP BY CaseAttribute.ProductLineCode, Attribute.Name
ORDER BY CaseAttribute.ProductLineCode, Sum(CaseAttribute.Deductions)
DESC;

...and get the Top 3 items by CaseAttribute.ProductLineCode then by
Attribute.Name. I've searched many sites but I can't seem to get the
concept down. Anyone care to help?

Thanks.

I assume you want the top 3 deduction totals for each
ProductLineCode/Attribute Name combination. Incidentally, "Name" is a
horrible name for a field. It's a reserved keyword and its use can cause
problems. Now, you can google for lists of reserved keywords, but the easier
way to avoid them is to be more descriptive with your field names - for
example "AttributeName" instead of "Name". I have aliased that column in
your above query to avoid this issue - take note before following the
instructions below.

First, save the above query, naming it "CaseDeductionTotals".
Then, create a new saved query called "RankedCaseDeductionTotals" using this
sql:
SELECT ProductLineCode, AttributeName, SumOfDeductions,
(SELECT Count(*) FROM CaseDeductionTotals As i
WHERE i.ProductLineCode = t.i.ProductLineCode AND
i.AttributeName= t.i.AttributeName AND
i.SumOfDeductions >= t.SumOfDeductions) As Rank
FROM CaseDeductionTotals As t

Then use this query to get the top 3 per group:
SELECT ProductLineCode, AttributeName, SumOfDeductions, Rank
FROM RankedCaseDeductionTotals
WHERE Rank < 4

Note that in the case of ties, you might not get only 3 results per group -
the data provides no way to break ties.

 

[Eric Brenner]

Bob, thanks for the queries.

One question though, I see that this query takes a big performace hit.
Is this normal?

SELECT ProductLineCode, AttributeName, SumOfDeductions,
(SELECT Count(*) FROM CaseDeductionTotals As i
WHERE i.ProductLineCode = t.i.ProductLineCode AND
i.AttributeName= t.i.AttributeName AND
i.SumOfDeductions >= t.SumOfDeductions) As Rank
FROM CaseDeductionTotals As t

Plus all the rankings are 1.

 

[Bob Barrows]

Bob, thanks for the queries.

One question though, I see that this query takes a big performace hit.
Is this normal?

Absolutely. Correlated subqueries are performance killers. Think about it:
that subquery (the query in the parentheses) has to be run for every record
in CaseDeductionTotals. Unfortunately, there is no other way to calculate a
rank in a query. You can get running totals in a report ...

SELECT ProductLineCode, AttributeName, SumOfDeductions,
(SELECT Count(*) FROM CaseDeductionTotals As i
WHERE i.ProductLineCode = t.i.ProductLineCode AND
i.AttributeName= t.i.AttributeName AND
i.SumOfDeductions >= t.SumOfDeductions) As Rank
FROM CaseDeductionTotals As t

Plus all the rankings are 1.

Not sure how that could be. Could you show me some records from
CaseDeductionTotals? Just show the records for a single
ProductLineCode/Attribute name combination that has multiple results with
.... wait, we grouped by ProductLineCode, AttributeName to get the
SumOfDeductions. There will only be a single row for each combination.
That's why the rankings are all 1.
Let's take a step back to the source data. Show me some rows of sample data
from your query in tabular format followed by the result you wish to obtain
from that sample data, again in tabular format.