请问如何从score表里查询各科成绩最高的学员的s_id,course,score

Question

表名为：score
scoreId     s_id        course               score
----------- ----------- -------------------- -----------
1           1           语文                   80
2           3           语文                   88
3           5           语文                   76
4           2           数学                   80
5           4           数学                   85
6           6           数学                   90
7           1           数学                   100
8           3           英语                   80
9           4           英语                   86
10          6           英语                   90结果为：
s_id        course               score
----------- -------------------- -----------
3           语文                   88
6           英语                   90
1           数学                   100
我是这样查出来的；select s.s_id,s.course,s.score 
from score s,(select course,max(score) score from score group by course) newtab
where s.course = newtab.course and s.score = newtab.score
请问这个结果如果不用连接查询仅仅只用group by能查出来吗？要怎么写？请高手指教

Answer 1

不用group by？好像很难
等待高手了

Answer 2

SELECT * FROM SCORE
WHERE SCORE IN (SELECT MAX(SCORE) FROM SCORE GROUP BY COURSE)

Answer 3

select s_id=(select min(s_id) from score where course=t.course and score=max(t.score)) course,score=max(score)
from score t
group by courseselect s_id,course,score
from score t
where not exists (select 1 from score where course=t.course and score>t.score)