I am trying to get the position of students in an exam. The SQL query I wrote uses the DENSE_RANK() function and it works properly just that it does not leave spaces after equal positions. I understand I can achieve that using the RANK() function.

I Tried using the RANK() function but to my surprise, I am getting a position which is even greater than the students roll. I don't know what I am doing wrong in the second query which uses the RANK() function. What I want to achieve now is to be able to rewrite my query properly with the RANK() function.

rem query with DENSE_RANK()

SELECT * FROM (SELECT studentid,stuname,session,f_score,stuclass,term,subject,final_ca,final_exam,f_scores = SUM(f_score),position = DENSE_RANK() OVER (ORDER BY SUM(f_score) desc) FROM assessment WHERE stuclass = '" & strclass & "' and term='" & strterm & "' and session='" & sess & "' GROUP BY studentid,f_score,stuclass,term,subject,final_ca,final_exam,stuname,session) T WHERE  studentid = '" & strid & "' "

rem Query with RANK() Function

SELECT * FROM (SELECT studentid,stuname,session,f_score,stuclass,term,subject,final_ca,final_exam,f_scores = SUM(f_score),position = RANK() OVER (ORDER BY SUM(f_score) desc) FROM assessment WHERE stuclass = '" & strclass & "' and term='" & strterm & "' and session='" & sess & "' GROUP BY studentid,f_score,stuclass,term,subject,final_ca,final_exam,stuname,session) T WHERE  studentid = '" & strid & "' "
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.