title: How do I query a mysql table to get the current value of a column and the max for the day
body: <p>I have a table that looks something like this. It updates every 5 minutes for each game_id</p>
  
  <pre>
  game_id  players   date
  12       420       2013-06-19 12:30:00
  13       345       2013-06-19 12:30:00
  14       600       2013-06-19 12:30:00
  12       375       2013-06-19 12:25:00     
  13       475       2013-06-19 12:25:00
  14       575       2013-06-19 12:25:00
  12       500       2013-06-19 12:20:00
  ...
  </pre>
  
  <p>I need a query for each game id, get the current players (latest timestamp) and the max for the day. so the results would look like this</p>
  
  <pre>
  game_id  max      current
  12       500      420
  13       475      345
  14       600      600
  </pre>
  
  <p>I tried something like this but, had no luck and can't figure it out :(</p>
  
  <p>select game_id, max(players) as max, players as current from players where date >= '2013-06-19' order by date desc group by game_id;</p>
  
  <p>Thanks for your help!</p>