Top 3 or Max 3 scores for each group.

[S]

I created a union query. lets call it "query 1"

It displays my data as below.

Student # Total
1 90
1 85
1 87
1 91
1 90
2 85
2 85
2 86
2 90
2 90
3 81
3 82
3 81

I need to create a query that gives me the top 3 scores for each student #.
I can only use 3 scores. If there are ties, I still can only have 3 scores.
Then i will create one more query that will add those 3 scores together.

Any help would be greatly appreciated.

 

[Bob Barrows [MVP]]

What should be done in this case?

4 98
4 90
4 90
4 90

 

[S]

I would just need
98
90
90

The two lowest scores are thrown out.

 

[Michel Walsh]

If you ONLY want their SUM, we can do without primary key, but with four
queries (which they will be called in cascade by the final one). I assume
the initial table is called s .


First query -- qs:

SELECT studentID, score,
(SELECT COUNT(*) FROM s AS a
WHERE s.studentID=studentID and s.score<=score) AS cumulN,
(SELECT SUM(a.score) FROM s AS a
WHERE s.studentID=studentID and s.score<=score) AS cumul
FROM s
GROUP BY studentID, score;



probably a very very slow query, with real data.


Second and third query, they just isolate the data in qs around the magic
number 3:

--qsLowerLimit:

SELECT studentID, MAX(cumulN) AS cumulLowN, MAX(cumul) AS cumulLow
FROM qs AS a
WHERE cumulN <=3
GROUP BY studentID;


-- qsHigherLimit:

SELECT qs.studentID, Max(qs.score) AS increment
FROM qs
WHERE qs.cumulN >3
GROUP BY qs.studentID;




and the top most query, returning the desired sum:


SELECT qsHigherLimit .studentID,
Nz(cumulLow, 0)+(3-Nz(cumulLowN, 0))*increment AS
theSumOfMaxTop3WithoutTie
FROM qsLowerLimit RIGHT JOIN qsHigherLimit
ON qsLowerLimit.studentID = qsHigherLimit.studentID;




NOTE: I assumed there is always AT LEAST 4 records, in the initial table,
s, for each student (else, the RIGHT join will have to be transformed into a
FULL OUTER join, which Jet does not support directly).



Vanderghast, Access MVP