CODEWITHSUNDEEP
sqlsql-servert-sql
Vitor Filho
Vitor Filho

Reputation: 11

SELECT TOP 3 Max Score

I have a question on how to express the output of some results? The problem is: There were a number of contests where participants each made number of attempts. The attempt with the highest score is only one considered. Write a query to list the contestants ranked in the top 3 for each contest. If multiple contestants have the same score in each contest, they are at the same rank.

Report event_id, rank 1 name(s), rank 2 name(s), rank 3 name(s). Order the contest by event_id. Name that share a rank should be ordered alphabetically and separated by a comma.

The database consists in only one table is:

event_id participant_name score
1434 Marcos 9.62821024209408
1434 Marcos 7.30471832966565
1434 Vitor 9.52821024209408
1434 Vitor 6.30471832966565

My Query is:

WITH max_score AS (
  SELECT event_id, participant_name, CAST(MAX(ROUND(score, 2,1)) AS DECIMAL (18,2)) AS score
  FROM scoretable 
  GROUP BY event_id, participant_name
),
Rank_table AS( 
  SELECT 
       event_id, 
       participant_name,
       score,
       DENSE_RANK() OVER   
        (PARTITION BY event_id ORDER BY score DESC) AS FinalRank
FROM max_score
)
SELECT * FROM Rank_table
WHERE FinalRank <= 3
ORDER BY event_id, score DESC;
event_id participant_name score FinalRank
1434 Aurora Leedom 9.98 1
1434 Shaunta Barletta 9.88 2
1434 Tricia Norgard 9.85 3
2626 Annita Tessier 9.95 1
2626 Loura Fortino, 9.95 1
2626 Christinia Padgett 9.94 2
2626 Ashlyn Cheatam 9.72 3

So I can make the results rank, my question is what resource could I use to make the result look like this:

event_id Rank 1 Rank 2 Rank 3
1434 Aurora Leedom Shaunta Barletta Tricia Norgard
2626 Annita Tessier, Loura Fortino Christinia Padgett Ashlyn Cheatam

Any help would be appreciated!

Upvotes: 0

Views: 1400

Answers (3)

Yasin Rahman
Yasin Rahman

Reputation: 11

For MySQL - This worked quite well!

WITH MaxScores AS (
    SELECT 
        event_id,
        participant_name,
        MAX(score) AS max_score
    FROM scoretable
    GROUP BY event_id, participant_name
)

, RankedParticipants AS (
    SELECT 
        event_id,
        participant_name,
        DENSE_RANK() OVER (PARTITION BY event_id ORDER BY max_score DESC) AS dr
    FROM MaxScores
)

SELECT 
    event_id,
    GROUP_CONCAT(CASE WHEN dr = 1 THEN participant_name END ORDER BY participant_name ASC) AS `Rank 1`,
    GROUP_CONCAT(CASE WHEN dr = 2 THEN participant_name END ORDER BY participant_name ASC) AS `Rank 2`,
    GROUP_CONCAT(CASE WHEN dr = 3 THEN participant_name END ORDER BY participant_name ASC) AS `Rank 3`
FROM RankedParticipants
WHERE dr <= 3
GROUP BY event_id
ORDER BY event_id;

Upvotes: 1

shivin mehta
shivin mehta

Reputation: 1

scoretable that i used as reference 

/* MYSQL SOLUTION */
/* using multiple common table expressions , dense_rank , JOIN and group_concat */

WITH t1 AS
(SELECT *,DENSE_RANK() OVER(PARTITION BY event_id ORDER BY score DESC) AS 'rk' FROM Scoretable),

t2 AS
(SELECT * FROM t1 WHERE rk<=3),

t3 AS 
(SELECT event_id , CASE WHEN rk=1 THEN p_Name ELSE NULL END AS 'first' FROM t2 WHERE rk=1 ),

t4 AS 
(SELECT event_id , CASE WHEN rk=2 THEN p_Name ELSE NULL END AS 'second' FROM t2 WHERE rk=2 ),

t5 AS 
(SELECT event_id , CASE WHEN rk=3 THEN p_Name ELSE NULL END AS 'third' FROM t2 WHERE rk=3 ),

t6 AS 
(SELECT t3.event_id , t3.first , t4.second , t5.third FROM t3 JOIN t4 ON t3.event_id = t4.event_id JOIN t5 ON t4.event_id=t5.event_id ORDER BY 1,2,3,4)

SELECT event_id , GROUP_CONCAT(DISTINCT first) AS 'rank 1' , GROUP_CONCAT(DISTINCT second) AS 'rank 2' , GROUP_CONCAT(DISTINCT third) AS 'rank 3'
FROM t6 GROUP BY 1 ORDER BY 1;

output

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

Use a pivot query to generate the 3 output columns you want:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY event_id ORDER BY FinalRank) dr
    FROM Rank_table
)

SELECT event_id,
       STRING_AGG(CASE WHEN dr = 1 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 1],
       STRING_AGG(CASE WHEN dr = 2 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 2],
       STRING_AGG(CASE WHEN dr = 3 THEN participant_name END, ',')
           WITHIN GROUP (ORDER BY participant_name) AS [Rank 3]
FROM cte
ORDER BY event_id;

Upvotes: 2

Related Questions