0

I'm having a problem with a query I'm running. Here's a dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=8dc0b4f201e7d25c8817dcecd35b47f0

Basically, I'm trying to keep the rows intact, what I mean is: You can see in the 1st query that playerID 147 set a score of 10450 on the 2018-03-24 13:37:02. However, sadly in the 2nd query the row breaks, I still get the min score I need, but the date is wrong. it sets 2018-03-05 16:24:28 even though it should be 2018-03-24 13:37:02.

I tried doing what's described here: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? but I can't get it to work. I've no idea how to rewrite my query in order for it to work. I'd appreciate any help in altering my query. Thanks for any help.

wtp2019
  • 5
  • 1
  • 3

1 Answers1

0

With this SELECT Statement

SELECT MIN(t.ID),t.Score,t.ChallengeId,t.playerID,MIN( from_unixtime(date) )
FROM `times` t inner Join
(SELECT MIN(Score) score,challengeID,playerID 
   from `times` 
   group by challengeID,playerID 
   Order by Score ASC) s
 ON t.challengeID = s.challengeID 
    and t.playerID = s.playerID
    and t.Score = s.score
GROUP BY t.Score,t.ChallengeId,t.playerID
order by t.Score

You get this result(Only the first are here Displayed

ID      Score   ChallengeId     playerID    from_unixtime(date) 
90488   10450   466                     28              2018-03-20 02:16:29 
92155   10450   466                     8           2018-03-24 02:05:18 
92448   10450   466                     147             2018-03-24 13:37:02 
92763   10450   466                     97              2018-03-24 19:15:42 
92787   10450   466                     410             2018-03-24 19:23:24 
85201   10460   466                     255             2018-03-06 05:13:46 
86256   10460   466                     66              2018-03-09 10:48:16 
92778   10460   466                     1846            2018-03-24 19:21:07 
84801   10470   466                     47              2018-03-05 16:29:41 
84804   10470   466                     944             2018-03-05 16:30:34 
85724   10470   466                     599             2018-03-07 08:07:30 
88139   10470   466                     1651            2018-03-15 11:16:54

DBfiddle new sample https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=de581e5a5b6fa9a184cc2e235337b2d5

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thanks, that seems to work, however, one more thing, in the fiddle you can see some duplicate playerids (890 for example), because they set the score multiple times, how do I only keep the row with the earliest date for the min score? – wtp2019 Oct 28 '19 at 12:28
  • Thats easy, you have to group them like i did in the edited Version. Ii decided to tale the earliest Timestamp there is, but you have to check it which you need. – nbk Oct 28 '19 at 13:00