How to get the two best students of each professor in SQL?

Question

I have a table with 3 fields like this:

ProfessorID    StudentID    Mark
P1             S1              9
P1             S2              8
P1             S3             10
P2             S1              7
P2             S2              2
P2             S3              4
P3             S4              5
P4             S1              6

A professor can teach many students, and vice versa, a student can learn from many professor. When a student learns from a professor, he gets his mark.

My problem is showing list of professors who teach at least 2 students, and 2 students who get best marks from those professors. In example, the query result of this table is:

ProfessorID    StudentID    Mark
P1             S1              9
P1             S3             10
P2             S1              7
P2             S3              4

I've tried some solutions but they don't work right.

How can I do this correctly?

Answer 1

SELECT ProfessorID, StudentID, MAX(Mark)
FROM table
GROUP BY ProfessorID, StudentID

Answer 2

Asuming you want it in SQL 2005 (or 2008), this will work as well

select *
from
(
select *
    ,rank() over  (partition by professorid order by mark desc) as ranking
    ,(select count(distinct studentid) 
     from marks m2 
     where m2.professorid = m1.professorid 
     group by professorid
     ) as students
from marks m1
) subs
where ranking < 3
and students > 2

-Edoode

Answer 3

declare  @table table (ProfessorID nvarchar(2), StudentID nvarchar(2),Mark int)

insert into @table
select 'P1', 'S1', 9
union all
select 'P1', 'S2', 8
union all
select 'P1', 'S3', 10
union all
select 'P2', 'S1', 7
union all
select 'P2', 'S2', 2
union all
select 'P2', 'S3', 4
union all
select 'P3', 'S4', 5
union all
select 'P4', 'S1', 6

select *
from @table o
where o.StudentID IN (select top 2 s.StudentID from @table s where s.ProfessorId = o.ProfessorId order by Mark DESC)
and o.ProfessorID IN (select p.ProfessorID from @table p group by p.ProfessorID having count(*) >= 2)

Answer 4

I just woke up... but here goes:

select *
FROM 
   (select professorID, count(distinct studentID) as studentsTaught
    FROM table
    ) s
WHERE s.studentsTaught = 2
UNION ALL    
SELECT top 2 *
FROM table
WHERE professorID IN (select professorID
                      FROM (
                            select professorID, count(distinct studentID) as studentsTaught
                            FROM table
                            ) s
                      )
WHERE s.studentsTaught = 2
ORDER BY mark

Should work... basically you're querying the professors and counting them as a subquery, then selecting the ones who have 2 students from that subquery. For their students you have to UNION them and find the same professors, but ORDER BY is good enough to get the two best students.