Calculating Rank at Sql server

Question

I have below requirement

I am using condition like rank() ( partition by Name order by sum(marks)) as rank, i am getting the rank value as Rank_Coming column, but i am expecting the value like Rank expected column.

Please suggest how to achieve this

Thanks

Answer 1

You rank expect is not GROUP BY Name -- PARTITION BY Name. It's ORDER BY Name

Try this:

DENSE_RANK() OVER(ORDER BY Name)

Answer 2

You need to get the SUM first and then use that in ranking.

;WITH CteSum AS(
    SELECT *,
        SumMarks = SUM(Marks) OVER(PARTITION BY Name)
    FROM tbl
),
CteRnk AS(
    SELECT *,
        rnk = DENSE_RANK() OVER(ORDER BY SumMarks DESC)
    FROM CteSum
)
SELECT * FROM CteRnk