Top N of group problem....

[equestrian]

I have qryPointsByDivision.

It shows: DivisionNum_fk, EntryNum_fk, SumofPoints

I need EntryNum_fk, and SumofPoints for the 2 highest SumofPoints for each DivisionNum_fk.

I put:

In (Select Top 2 [SumofPoints] From qryPointsByDivision  Order By [SumofPoints] Desc)

in the criteria for SumofPoints, but this returns the top 2 SumofPoints for the entiry qryPointsByDivision. I know that I need to somehow group this, but I am not sure how to enter it in my query.

The sql code is:

SELECT qryPointsByDivision.DivisionNum_fk, qryPointsByDivision.EntryNum_fk, qryPointsByDivision.SumOfPoints
FROM qryPointsByDivision
WHERE (((qryPointsByDivision.SumOfPoints) In (Select Top 2 [SumofPoints] From qryPointsByDivision  Order By [SumofPoints] Desc)));

Can anyone help me with this?

 

[dhookom]

How about:

SELECT qryPointsByDivision.DivisionNum_fk, qryPointsByDivision.EntryNum_fk, qryPointsByDivision.SumOfPoints
FROM qryPointsByDivision
WHERE qryPointsByDivision.SumOfPoints In (Select Top 2 [SumofPoints] From qryPointsByDivision q WHERE q.DivisionNum_fk = qryPointsByDivision.DivisionNum_fk  Order By [SumofPoints] Desc);

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way:

SELECT A.DivisionNum_fk, A.EntryNum_fk, A.SumOfPoints
FROM qryPointsByDivision AS A
INNER JOIN qryPointsByDivision AS B ON A.DivisionNum_fk=B.DivisionNum_fk AND A.SumOfPoints<=B.SumOfPoints
GROUP BY A.DivisionNum_fk, A.EntryNum_fk, A.SumOfPoints
HAVING Count(*)<=2;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[equestrian]

Thanks. I used Duane's code and it worked great.

 

[dhookom]

PH's code might be more efficient if your query runs too slow.

Duane
Hook'D on Access
MS Access MVP