SQL Query - Rank showing only 1 rank for all records

Question

I am trying to perform ranking based on some calculation of already existing columns. I tried using the SQL RANK() function however it is showing the result as 1 for all entries even if the value of the order by (score column) is different. Please see the details below:

1. qu_point and ti_points are calculated columns
2. score column is again a derived column, however, simply sum of two columns mentioned in point 1.

I have used the SQL query as follow:

use EFR_DB
GO

select d.serial, d.question_set_id, d.correct_answers, d.total_questions, d.time_taken_seconds, q.total_time_in_secs,
(cast(d.correct_answers as float)/d.total_questions) as qu_point, ((q.total_time_in_secs-d.time_taken_seconds)/q.total_time_in_secs) as ti_point,
(((cast(d.correct_answers as float)/d.total_questions)*2) + ((q.total_time_in_secs-d.time_taken_seconds)/q.total_time_in_secs)) as score,

rank() over (partition by d.question_set_id order by score)

from daily_quiz_record d join Question_set q 
on q.question_set_id=d.question_set_id

Please help me how can I do the raking which is partitioned by question_set_id and ranked on the basis of the score.

Screenshot attached for your reference. enter image description here

Answer 1

You can’t use an alias defined in the select clause in the same clause. I suppose that one of your table has a column called score, otherwise your query would error - so this existing column is being used for ordering instead of the computed value.

Since your expression is lengthy, it is simpler to turn the query to a subquery, and rank in the outer query:

select 
    t.*,
    rank() over(partition by question_set_id order by score) rn
from (
    -- your existing query (without rank)
) t