Problem with Rank over partition function
I would like my report to display module results for students. Each student has to be displayed together with the modules done, also numbering the exam attempts for each module, i.e. keeping track of the number of times a student has attempted an exam for each module as shown below:
student id Module Attempt Mark
1000 English 1 20
1000 English 2 40
1000 English 3 65
1000 Maths 1 55
2000 English 1 85
2000 Maths 1 70
Attempt does not exist in a database field. It has to be derived or calculated using the existing database fields (student id, module, mark). I have tried using Rank function RANK() OVER(PARTITION BY Student ID ORDER BY Module) but it was ranking for all students instead of per student as shown above. How can I achieve this (numbering each Attempt for each student per module)? Thanks.