For example: There are 3 students, they have a few (each student can have different number of course) courses to test, now need to:
1.Group students by their average courses score.
2.Find the top 1 student with above Rank then list the student's all courses with score
Jack course1:70 course2:100
Min course1:80
May course1:60 course2:40 course3:100
For quiz 1, I did like:
SELECT mean(score) FROM table GROUP BY student
It's done.
For quiz 2, I can first get top1 student by:
SELECT top(mean, 1) FROM
(
SELECT mean(score) FROM table GROUP BY student
)
but this will remove the student key, how can I do like:
SELECT student, score FROM
(
SELECT top(mean, 1), st
FROM
(
SELECT mean(score),student as st FROM table GROUP BY student
)
) WHERE student=st
As I can see, the student cannot be kept in the first SELECT because "mixing aggregate and non-aggregate queries" error.
Thanks
