September 19, 2003 at 9:50 am
I need some help in solving a problem, and thanks in advance.
Here is a simplified version of a problem I have.
I have a table that looks like this:
Student Test Marks AvgMarks
---------- ------ ---------- ---------
A 1 20
A 2 30
A 3 40
A 4 50
A 6 80
B 1 15
B 2 25
B 4 35
B 5 60
C 3 75
C 5 90
C 6 95
What I would like is to update the AvgMarks field for each student at each test with the avg marks for current and upto two prior tests.
So for a, at test 4 that would be average of 4, 3, and 2 hence, (50+40+30) / 3 = 40. At test 6 that is average of 6, 4 and 3
hence (80+50+40 ) / 3 = 56.66. At test 2 , since there is only one prior instance, the average would be (20+30) / 2 = 25.
And so on.
The table should look like this after the updates.
Student Test Marks AvgMarks
---------- ----------- ----------------------
A 1 20 20
A 2 30 25
A 3 40 30
A 4 50 40
A 6 80 56.66
B 1 15 15
B 2 25 20
B 4 35 25
B 5 60 40
C 3 75 75
C 5 90 82.5
C 6 95 86.6
I cannot see any way to get the SQL to average only the top 3 records for each student. I might do with the temp tables or cursors
but is there any way I can do this better way??
September 19, 2003 at 10:29 am
SELECT Student, Test, Marks,
(SELECT AVG(Marks)
FROM
(SELECT TOP 3 Marks
FROM Marks
WHERE Student = m.Student AND Test <= m.Test
ORDER BY Marks DESC) a)
FROM Marks m
--Jonathan
Edited by - jonathan on 09/19/2003 10:37:03 AM
--Jonathan
September 22, 2003 at 6:59 am
Jonathan,
Don't you mean
ORDER BY Test DESC
Your solution works because the marks are in ascending order with Test.
Far away is close at hand in the images of elsewhere.
Anon.
September 22, 2003 at 9:09 am
quote:
Jonathan,Don't you mean
ORDER BY Test DESCYour solution works because the marks are in ascending order with Test.
Rereading the question... Yes, you're right. I misunderstood that the OP wanted the top three scores, not the last three tests. Thanks!
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply