CODEWITHSUNDEEP
sqldense-rank
Pseudorandom
Pseudorandom

Reputation: 726

How to rank/dense_rank by grouping column

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

Upvotes: 1

Views: 934

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

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

Upvotes: 1

radar
radar

Reputation: 13425

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

Upvotes: 1

Related Questions