Selecting Top N records

[cthang]

Hi,

I have a Student course database and it holds the courses and grades for their courses.

What I want to do is select top 3 marks for each student in the database. Is there any way I can do that with a SQL statement?

Thnxs

[Strike]

Depends on what SQL server you are using. The easiest way is to just order by grade and then, if your SQL server allows it, limit the selection to only N records. For example, in MySQL:


select grade from grades where studentid = 1 order by grade limit 3


(disclaimer: untested, off the top of my head, but should work)

[cthang]

Sorry, I forgot to mention that I am using SQL 2000. The limit doesn't work in SQL 2000.

[hanzo]

try

SELECT TOP 3 *
FROM <table>
ORDER BY grades DESC

[cthang]

that would only return the top three marks, not the top three marks per student.

thnxs

[DNAunion2000]

I really have no idea if this will work, but it's worth a try.


SELECT *
FROM grades A
WHERE A.studID + A.courseID IN
(SELECT TOP 3 B.studID + B.courseID AS compkey
FROM grades B
WHERE B.studID = A.studID
ORDER BY B.grade DESC) ;


PS: This assumes a "bridge" table called Grades with a composite PK of StudID and CourseID, with a non-key attribute Grade (this table would "span the gap" between a Students table with PK of StudID and a Courses table with a PK of CourseID, as a means of breaking up the many-to-many relationship between Students and Courses into two one-to-many relationships).