/*
> 3 skaters each compete in an event, and 5 judges give them marks
> which rank
> each against the other:
> Skater #   j1      j2      j3      j4      j5
> 1          3       1       1       1       2
> 2          1       2       2       2       1       
> 3          2       3       3       3       3       
> In this case,
> skater one got more 1st places than skater 2 or 3, so she gets 3/1, 1/2, 1/3
> skater 2 got more 2nd's than 1st, so she gets 3/2, 2/1
> and the last got 4/3, 1/2
> so skater 1 was the best, 2 was second, and 3 was last
> So I want to return for each skater the number of marks (ordinals)
> given by each judge so that I can figure out the placements.
> 
*/
create table #skate_event
        (Skater tinyint, j1 tinyint, j2 tinyint, j3 tinyint, j4 tinyint, j5 tinyint)
insert #skate_event values( 1           ,3      ,1      ,1      ,1      ,2)
insert #skate_event values( 2           ,1      ,2      ,2      ,2      ,1)
insert #skate_event values( 3           ,2      ,3      ,3      ,3      ,3)

SELECT skater, sum(case when rank = 1 then 1 else 0 end) as rank1,
        sum(case when rank = 2 then 1 else 0 end ) as rank2,
        sum(case when rank = 3 then 1 else 0 end) as rank3
FROM (
SELECT skater, j1 FROM #skate_event
UNION ALL
SELECT skater, j2 FROM #skate_event
UNION ALL
SELECT skater, j3 FROM #skate_event
UNION ALL
SELECT skater, j4 FROM #skate_event
UNION ALL
SELECT skater, j5 FROM #skate_event
) AS t(skater, rank)
GROUP BY skater
ORDER BY 1, 2 desc, 3 desc, 4 desc


SELECT Skater,
        cast(rank1 as varchar) + '/1, ' + cast(rank2 as varchar) + '/2, ' +
        cast(rank3 as varchar) + '/3' AS final_ranking
FROM (
SELECT TOP 100 PERCENT
        skater, sum(case when rank = 1 then 1 else 0 end),
        sum(case when rank = 2 then 1 else 0 end ),
        sum(case when rank = 3 then 1 else 0 end)
FROM (
SELECT skater, j1 FROM #skate_event
UNION ALL
SELECT skater, j2 FROM #skate_event
UNION ALL
SELECT skater, j3 FROM #skate_event
UNION ALL
SELECT skater, j4 FROM #skate_event
UNION ALL
SELECT skater, j5 FROM #skate_event
) AS t(skater, rank)
GROUP BY skater
ORDER BY 1, 2 desc, 3 desc, 4 desc
) AS event(skater, rank1, rank2, rank3)

This page was last updated on May 01, 2006 04:28 PM.