How to rank/dense_rank by grouping column

Question

In what possible way/method using sql query to have this kind of result. enter image description here

Ranking the judges scores to each contestants, sorting the finalist showing the final ranking.

This @Prince Jea, given a solution but its not the actual result that I have been expecting, although its correct. Still, im looking for desired output.

SQL QUERY

    SELECT ContestantID,JudgeID,ScorePoints, 
    RANK() OVER (ORDER BY ScorePoints DESC) AS xRank,
    DENSE_RANK() OVER (ORDER BY ScorePoints DESC) AS fRank
FROM 
( 
SELECT ContestantID , ScorePoints, JudgeID
FROM Score
) AS a
ORDER BY 1

Result

enter image description here

I have prefer some materials on SQL Fiddle http://www.sqlfiddle.com/#!6/30d03/2

do you need a query for exactly 3 judges or does the query need to be able to handle a dynamic # of judges? — FuzzyTree, Sep 30 '14 at 18:02
a dynamic # of judges sir. but if what's the best ill take it. — Pseudorandom, Sep 30 '14 at 18:04

score 1 · Accepted Answer · answered Sep 30 '14 at 18:17

First CTE is doing PIVOT on Judge column

Second CTE is calculating the rank for each contestant based on judge scores

Third CTE is calculating the final score for each contestant

  ; WITH CTE 
    AS
    (
    SELECT ContestantID,
           MAX(CASE when JudgeID =1 THEN ScorePoints END ) as [JudgeID#1],
           MAX(CASE when JudgeID =2 THEN ScorePoints END ) as [JudgeID#2],
           MAX(CASE when JudgeID =3 THEN ScorePoints END ) as [JudgeID#3]
    FROM Score
    Group by ContestantID
    )
    ,  CTE2 AS
    (
    SELECT ContestantID,
           [JudgeID#1],
           RANK() OVER   ( Order by JudgeID#1 desc ) as 'Rank#1',       
           [JudgeID#2],
           RANK() OVER   ( Order by JudgeID#2 desc ) as 'Rank#2',       
           [JudgeID#3],
           RANK() OVER   ( Order by JudgeID#3 desc ) as 'Rank#3'

    FROM CTE
    )
    , CTE3 as
    (
    SELECT ContestantID,
          SUM (Rank#1+ Rank#2 + Rank#1) as total
    FROM CTE2
    GROUP BY ContestantID
    )
    SELECT CTE2.*, CTE3.total as 'Final Score'
    FROM  CTE2
    JOIN CTE3 
    ON CTE2.ContestantID = CTE3.ContestantID
    ORDER BY ContestantID

EXCELLENT! this is what im looking for. i accept your answer. thank you so much. — Pseudorandom, Sep 30 '14 at 18:23

score 1 · Answer 2 · edited May 23 '17 at 12:21

Here's a dynamic pivot query that works with any # of Judges. The pivot code is adapted from https://stackoverflow.com/a/12505138/3574819.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @cols3 AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(JudgeID) 

                    from Score
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


select @cols2 = STUFF((SELECT distinct '+' + QUOTENAME('rn' + CONVERT(varchar(1),JudgeID)) 
                    from Score
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols3 = STUFF((SELECT distinct ', ' + QUOTENAME(JudgeID) + ', rank() over (order by  ' + QUOTENAME(JudgeId) + ' desc) ' + QUOTENAME('rn' + CONVERT(varchar(1),JudgeID))

                    from Score
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT * ,
        rank() over (order by ' + @cols2 + ') rn, 
        ( ' + @cols2 + ' ) fn
        FROM (SELECT ContestantID,' + @cols3 + ' from 
             (
              select ContestantID, ScorePoints, JudgeID
              from Score
            ) x
            pivot 
            (
                max(ScorePoints)
                for JudgeID in (' + @cols + ')
            ) p ) t1'

execute(@query)

http://www.sqlfiddle.com/#!6/87b45/1

How to rank/dense_rank by grouping column

2 Answers2