students position in exams base on results.

[Tawiah]

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 & "' "

[rproffitt]

https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql and other docs made no mention of added spaces.

When I need formatting I do that in my report software.