Top 3 or Max 3 scores for each group.

  • Thread starter Thread starter S
  • Start date Start date
S

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.
 
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
 
Back
Top