Getting Distinct Max Values from Multiple Columns

Question

I'm working on a sports database, and I want to write a query that will return the name and the statistical value for certain categories. For example, goal leader, assist leader, points leader, +/- leader, penalty minutes leader, etc. I am using a table called NJDSkaters which contains player names and stats from a specific team. Here is the query code:

SELECT CONCAT(PlayerName,' - ',Goals) AS GoalLeader, CONCAT(PlayerName,' - ',Assists)
    CONCAT(PlayerName,' - ',Points) AS PointsLeader
FROM NJDSkaters
WHERE Goals = (SELECT DISTINCT MAX(Goals) FROM NJDSkaters)
    OR Assists = (SELECT DISTINCT MAX(Assists) FROM NJDSkaters)
    OR Points = (SELECT DISTINCT MAX(Points) FROM NJDSkaters);

Here is a snippet from my skater register table which will show the players who should be returned by this query:

As you can see, my desired return query should have 'Ilya Kovalchuk - 37' returned as GoalLeader, 'Patrik Elias - 52' as AssistLeader, and 'Ilya Kovalchuk - 83' as PointsLeader. Running the query does provide these results, but there is extra information included that I do not want, as you can see here:

My question is, how do i get rid of the excess information? I only want the leaders in each category, and I don't want to see the #2 player listed, even if that player is #1 in some other category. Essentially, what I'm saying, is I want only 1 row in this table. Before, I had code that would return all players with the leaders at the top, so this code is a step closer to my desired result, but now I'm stuck. Searching for an answer to this problem has been challenging, as finding a way to ask it generally is difficult.

fthiella fthiella · Accepted Answer · 2013-04-16T20:19:52

You need to PIVOT your data, I would use something like this:

SELECT
  MAX(CASE WHEN NJDSkaters.Goals=mx.goals
       THEN CONCAT(PlayerName,' - ', NJDSkaters.Goals) END) GoalLeader,
  MAX(CASE WHEN NJDSkaters.Assists=mx.assists
       THEN CONCAT(PlayerName,' - ', NJDSkaters.Assists) END) AssistsLeader,
  MAX(CASE WHEN NJDSkaters.Points=mx.points
       THEN CONCAT(PlayerName,' - ', NJDSkaters.Points) END) PointsLeader
FROM
  NJDSkaters INNER JOIN (
    SELECT MAX(Goals) goals, MAX(Assists) assists, MAX(Points) points
    FROM NJDSkaters) mx
  ON NJDSkaters.Goals=mx.goals
     OR NJDSkaters.Assists=mx.assists
     OR NJDSkaters.Points=mx.points

Please see fiddle here. You might also want to use GROUP_CONCAT instead of MAX in case that more than one player shares the same maximum value:

SELECT
  CONCAT(GROUP_CONCAT(CASE WHEN  NJDSkaters.Goals=mx.goals
       THEN PlayerName END), ' - ', mx.goals) GoalLeader,
  CONCAT(GROUP_CONCAT(CASE WHEN NJDSkaters.Assists=mx.assists
       THEN PlayerName END), ' - ', mx.assists) AssistsLeader,
  CONCAT(GROUP_CONCAT(CASE WHEN NJDSkaters.Points=mx.points
       THEN PlayerName END), ' - ', mx.points) PointsLeader
FROM
  NJDSkaters INNER JOIN (
    SELECT MAX(Goals) goals, MAX(Assists) assists, MAX(Points) points
    FROM NJDSkaters) mx
  ON NJDSkaters.Goals=mx.goals
     OR NJDSkaters.Assists=mx.assists
     OR NJDSkaters.Points=mx.points

A little explanation:

The subquery mx will return the maximum number of goals, the maximum number of assists, and the maximum points
I'm joining the table NJDSkaters with this subquery to return all of the rows that have the maximum number of goals OR the maximum number of assists OR the maximum points
CASE WHEN NJDSkaters.Goals=mx.goals THEN PlayerName END will return the PlayerName if that player has the maximum number of goals, otherwise it will return NULL. The same goes for assists and points.
using GROUP_CONCAT I'm concatenating all of the players names returned by the CASE WHEN. GROUP_CONCAT will skip NULL values and will only concatenate players that have the maximum value for their category
using CONCAT I'm concatenating the string returned by the GROUP_CONCAT above with the maximum value for each category.

Getting Distinct Max Values from Multiple Columns

2 Answers