Ranking your Records

There are some senarios where we are asked to fetch the second highest earning employee from the list of employees.
Here is the scenario I had to work: There are three tables, 1. Student, 2.Exam, 3.StudentExam. Student has all the student names and is independent table. Exam has all the exam names and is independent. StudentExam has the marks earned by each student in exams he has taken and I should say acts as a middle table between student and Exam and is dependent on both. I need to fetch the higest scorer for each exam and display his name. I tried group by but it shows all the records. After a short search in the internet I found this wonderful function in SQL 2005 (not sure if exists in prior version of sql server), Rank(). With this my query has become quite simple. Here is what I wrote in order to achieve the above criteria.

SELECT STUDENT, EXAM, MARKS FROM
(
SELECT STUDENT.NAME STUDENT, EXAM.NAME EXAM,
RANK () OVER(PARTITION BY EXAM.NAME ORDER BY MARKSSCORED DESC) AS RANK,
MAX(MARKSSCORED) AS MARKS
FROM STUDENT, EXAM, STUDENTEXAM
WHERE STUDENT.STUDENTID = STUDENTEXAM.STUDENTID
AND EXAM.EXAMID = STUDENTEXAM.EXAMID
GROUP BY EXAM.NAME, STUDENT.NAME, MARKSSCORED
) TMP
WHERE RANK = 1