Need Help With Top 2 By group

[msadiqrajani]

Hello


I have data set with below formatting and i am trying create query to get top two of each group(group by div and sub div)

Suppose I have this data:

DIVISION	SUBDIVISION	TRADE	SumOfTotProblems
IND	INDA	P1	20
IND	INDA	P3	12
IND	INDB	P1	15
IND	INDA	P2	25
IND	INDB	P3	15
IND	INDB	P2	30
AUS	AUSA	P1	23
AUS	AUSA	P3	37
AUS	AUSA	P2	27

Then result should be:

DIVISION	SUBDIVISION	TRADE	SumOfTotProblems
IND	INDA	P2	25
IND	INDA	P1	20
IND	INDB	P2	30
IND	INDB	P1	15
AUS	AUSA	P3	37
AUS	AUSA	P2	27

I tried this query....but its not working and throwing error:

Error:
this expression is typed incorrectly or it is too complex to be evaluated

Query:

SELECT Division, Subdivision , Trade, SumOfTotProblems
FROM Helpertop5 
WHERE
SumOfTotProblems IN 
(SELECT TOP 3 SumOfTotProblems
FROM Helpertop5 HT WHERE Helpertop5.Division = HT.Division AND Helpertop5.Subdivision = HT.Subdivision
ORDER BY Helpertop5.SumOfTotProblems DESC)
ORDER BY Helpertop5.SumOfTotProblems DESC;

Please help me to figure out how I can do this.
Thanks

[orange]

See the TOP n per Group info at http://allenbrowne.com/subquery-01.html

[June7]

I built a table per the example and pasted your SQL into query. It ran without error message but returns all 9 records.

This returns 7 records because there is a tie.

SELECT *
FROM Helpertop5
WHERE SumOfTotProblems IN
(SELECT Top 2 SumOfTotProblems AS S_D FROM Helpertop5 AS HT
WHERE HT.SubDivision = Helpertop5.SubDivision And HT.Division = Helpertop5.Division
ORDER BY SumOfTotProblems DESC)
ORDER BY SubDivision, Division;

Tie breaker would require a unique ID. Review http://allenbrowne.com/subquery-01.html