How can I select the value of a column depending on where the maximum value between a second and thi-CodePudding

I am a first year student learning SQL. I would like to know how I can select the data_jogo (date_game) value, where a certain player achieved his maximum score, in a match. This player can appear as player 1 (id_jogador1) or player 2 (id_jogador2). Table name is "partidas", translating it means matches, and "pontuacao_jog1" means player 1's score, and "pontuacao_jog2" is the same for player 2.

enter image description here

I tried this way, but doesn't work (#1111 - Uso inválido de função de agrupamento (GROUP)):

SELECT partidas.data_jogo AS Date
FROM partidas 
WHERE (partidas.id_jogador1 = 'CR7' OR partidas.id_jogador2 = 'CR7')
AND GREATEST (max (partidas.pontuacao_jog1), max (partidas.pontuacao_jog2));

Can someone help please?

Thank you, João

CodePudding user response：

You can use UNION ALL to get a players scores in an easy way. Then you can order the scores and use LIMIT to get the row with the maximum score:

WITH scores AS
(
  SELECT pontuacao_jog1 as score, data_jogo
  FROM partidas
  WHERE id_jogador1 = 'CR7'
  UNION ALL
  SELECT pontuacao_jog2 as score, data_jogo
  FROM partidas
  WHERE id_jogador2 = 'CR7'
)
SELECT *
FROM scores
ORDER BY score DESC
LIMIT 1;

Only problem: If there are two days with the same maximum score, you'll only show one of them arbitrarily chosen. If you want to show both:

WITH scores AS ( <same as above> )
SELECT *
FROM scores
WHERE score = (SELECT MAX(score) FROM scores);