I have the following case (the example I am demonstrating is on a dummy domain because for NDA reasons etc.).
I have access to a production DB holding student data in the following shape:
Student_ID Course_Name Total_Average_Grade
1 Maths 19,1
1 Physics 0
1 Biology 0
2 Physics 18,5
2 Chemistry 0
3 Maths 19,4
3 Literature 0
3 Physics 0
I want to retrieve say the 10 students with the highest total average grade, and for those 10 retrieve the whole list of courses they took. I want to run this query on a monthly basis.
For example, on the above table, if I wanted to select the top 2 students, the output would be:
Student_ID Course_Name Total_Average_Grade
3 Maths 19,4
3 Physics 0
3 Biology 0
1 Maths 19,1
1 Literature 0
1 Physics 0
Note that I do not have rights to create either views or stored procedures on this database , which would be an easy way to overcome the limitation of not being able to perform subqueries on a (select top n / limit n) selection (for example, I would be able to automatically create a monthly view which I would use to join with a plain select top 10 averages etc.).
Is there any elegant solution on this task?
For the record, I am using Sybase 15.4 as client to access the Sybase IQ production databse.
I think you can use a query like this:
select *
from (
select *,
row_number() over (partition by Course_Name order by Total_Average_Grade desc) as rn
from yourTable) t
where t < 11;