Convert columns to a single row and select unique entries

Question

Currently, the table looks like this. This is basically a list of leaderboards entries everything looks fine at this point.

+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
| entry | challenge_level | proof | player1_name | player2_name | player3_name | player4_name |   submission_date    | approved |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
|     8 |              52 | url   | PLAYER_A     | PLAYER_D     | PLAYER_B     | PLAYER_C     | 2018-07-16T16:14:01Z | true     |
|     9 |              60 | url   | PLAYER_C     | PLAYER_K     | PLAYER_X     | PLAYER_Y     | 2018-07-16T16:33:10Z | true     |
|    11 |              51 | url   | PLAYER_A     | PLAYER_B     | PLAYER_C     | PLAYER_D     | 2018-07-16T17:42:13Z | true     |
|    13 |              45 | url   | PLAYER_C     | PLAYER_H     | PLAYER_J     | PLAYER_D     | 2018-07-16T18:26:59Z | true     |
|    14 |              61 | url   | PLAYER_A     | PLAYER_C     | PLAYER_I     | PLAYER_B     | 2018-07-16T21:09:55Z | true     |
|    15 |              36 | url   | PLAYER_A     | PLAYER_C     | PLAYER_D     | PLAYER_B     | 2018-07-17T08:24:37Z | true     |
|    16 |              62 | url   | PLAYER_E     | PLAYER_C     | PLAYER_F     | PLAYER_G     | 2018-07-17T09:37:13Z | true     |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+

The problem begins when I would like to have my desired result formatted as following, desired result

+-------+-----------------+-------+--------------+----------------------+----------+
| entry | challenge_level | proof | PLAYER_NAME  |   submission_date    | approved |
+-------+-----------------+-------+--------------+--------------+--------------+---+
|     14 |              61  | url   | PLAYER_A     | 2018-07-16T16:14:01Z | true     |
|     14 |              61  | url   | PLAYER_B     | 2018-07-16T16:33:10Z | true     |
|   16   |              62  | url   | PLAYER_C     | 2018-07-16T17:42:13Z | true     |
|    8  |              52  | url   | PLAYER_D     | 2018-07-16T18:26:59Z | true     |
|    16  |              62  | url   | PLAYER_E     | 2018-07-16T21:09:55Z | true     |
|    16  |              62  | url   | PLAYER_F     | 2018-07-17T08:24:37Z | true     |
|    16  |              62  | url   | PLAYER_G     | 2018-07-17T09:37:13Z | true     |
+-------+-----------------+-------+--------------+--------------+--------------+---+

On top of that, there would be one important factor to consider, players can appear multiple times so, for example, PLAYER_A is listed in multiple entries but the result should be the highest challenge_level he did - player name has to be unique and cant appear multiple times in the desired result.

I do not know where to start with this one, any hints will be valuable.

SqlFiddle >> Table Schema >>

Unpivot: https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table Get records with maximum value in a field: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Shadow, Jul 23 '18 at 14:12
"Added an answer it seems right, right? " No @KavvsonEmpcraft you are misusing MySQL's GROUP BY "feature".. Try it running with `SET SESSION sql_mode = CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY')`; before you run the query. — Raymond Nijland, Jul 23 '18 at 14:30
"On top of that, there would be one important factor to consider, players can appear multiple times so, for example, PLAYER_A is listed in multiple entries but the result should be the highest challenge_level he did " What about ties? I mean what should happen if a unique player has two or the same highest challenge_level — Raymond Nijland, Jul 23 '18 at 14:34
@RaymondNijland if tie - consider submission date? sounds logical forgot about this one — Kavvson Empcraft, Jul 23 '18 at 14:35
"if tie considers submission date? sounds logical " Yes but iam not going to think for you how the results should be if you know what i mean. Also what MySQL version do you use `SELECT VERSION()` — Raymond Nijland, Jul 23 '18 at 14:36
How did `9 | 60 | url | PLAYER_B | 2018-07-16T16:33:10Z | true` (second record in the expected output) get entry id 9? In the example data there isn't PLAYER_B in anny of the columns for entry id 9... The query that is needed revolve this data and requirements will be pretty complex because and first look it's involves unpivotting, user variables, GROUP BY with MAX and JOIN — Raymond Nijland, Jul 23 '18 at 15:04
The values are not valid was just an example but it should be 14 | 61 | url | PLAYER_A in this example — Kavvson Empcraft, Jul 23 '18 at 15:06
In case it wasn't mentioned already (strange), this kind of problem is symptomatic of poor schema design. — Strawberry, Jul 23 '18 at 15:19
if you want to show input data and expected results ensude they are consistent with each other and that the results are valid. — Lelio Faieta, Jul 23 '18 at 15:31
Why **PLAYER_X** is not in the desired output ? what rule filter him out? Same question about **_Y, _H, _J, _K** ? http://sqlfiddle.com/#!9/869812/19 — Alex, Jul 23 '18 at 15:43
examples, even when quick should be consistent and proper. If you provide raw data it should correlate to desired output. What about Kaavson answer? what about my fiddle I've posted in comment? — Alex, Jul 23 '18 at 15:50
@Alex updated the desired table just the submission date is random — Kavvson Empcraft, Jul 23 '18 at 17:00
did you check my fiddle? your update did not change the picture. Still no **_Y, _H, _J, _K, _X** values in result when they exist in initial dataset. — Alex, Jul 23 '18 at 17:07
That is not my values. That is your data. Your requirements are not clear. You did not specify what should happen with entry 15 when challenge_level=36 is not a max for any player - do you want to exclude them? or to output? And you are kind ignoring my question 2nd time about **_Y, _H, _J, _K, _X** players. If you do not need help anymore - feel free to say that to not waste our time. — Alex, Jul 23 '18 at 17:58
@Alex It should display all players and find their max challenge_level, they should appear once. So the lower level should not be taken into account — Kavvson Empcraft, Jul 23 '18 at 18:07

score 1 · Accepted Answer · answered Jul 23 '18 at 18:09

http://sqlfiddle.com/#!9/869812/21

SELECT l.`entry`,
  l.`challenge_level`,
  l.`proof`,
  l_max.player,
  l.`submission_date`,
  l.`approved`
FROM leaderboards l
INNER JOIN
(SELECT player, MAX(challenge_level) as `level`
FROM (SELECT l1.`entry`,
  l1.`challenge_level`,
  l1.`proof`,
  l1.`player1_name` as player,
  l1.`submission_date`,
  l1.`approved`
FROM leaderboards l1
UNION ALL
SELECT l2.`entry`,
  l2.`challenge_level`,
  l2.`proof`,
  l2.`player2_name`,
  l2.`submission_date`,
  l2.`approved`
FROM leaderboards l2
UNION ALL
SELECT l3.`entry`,
  l3.`challenge_level`,
  l3.`proof`,
  l3.`player3_name`,
  l3.`submission_date`,
  l3.`approved`
FROM leaderboards l3
UNION ALL
SELECT l4.`entry`,
  l4.`challenge_level`,
  l4.`proof`,
  l4.`player4_name`,
  l4.`submission_date`,
  l4.`approved`
FROM leaderboards l4
) l_all
GROUP BY l_all.player) l_max
ON l.challenge_level = l_max.level
  and (l.player1_name = l_max.player
       OR l.player2_name = l_max.player
       OR l.player3_name = l_max.player
       OR l.player4_name = l_max.player)

Convert columns to a single row and select unique entries

1 Answers1