CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Candice
Candice

Reputation: 25

How do I query a mysql table to get the current value of a column and the max for the day

I have a table that looks something like this. It updates every 5 minutes for each game_id

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
...

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

game_id  max      current
12       500      420
13       475      345
14       600      600

I tried something like this but, had no luck and can't figure it out :(

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;

Thanks for your help!

Upvotes: 0

Views: 2535

Answers (4)

zod
zod

Reputation: 12417

select game_id, max(players) as max, players as current from players  where date >= '2013-06-19' group by game_id  order by date desc ;

Upvotes: 1

axiopisty
axiopisty

Reputation: 5137

select
  t.game_id
  , maxes.max_players as max
  , t.players as current_players as current
from
  (
    select
      t.game_id
      , max(t.players) as max_players
      , max(t.date) as max_date
    from
      t
    where
      t.game_id = :game_id
      and t.date >= :todays_date
    group by
      t.game_id
  ) maxes
  inner join t on t.game_id = maxes.game_id and t.date = maxes.max_date
where
  t.date >= :todays_date

I don't have a mysql db configured to test this right now but I think it will give you the desired results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

To get the last value, you need a trick of one sort or another. Instead of using a join, this version uses the substring_index()/group_concat() trick:

select game_id, max(players) as MaxPlayers,
       substring_index(group_concat(players order by date desc), ',', 1) + 0 as Latest
from players
group by game_id;

The nice thing about this approach is that it is guaranteed to work and does not require any additional joins.

In particular, it does not use the MySQL extension that allows columns to be included in the select clause without their being in the group by clause. The results are indeterminate when there are multiple values, as expressly stated in the documentation:

You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Upvotes: 1

sinisake
sinisake

Reputation: 11318

http://www.sqlfiddle.com/#!2/e5157/5

 select game_id, max(players) as maximum, 
players as current 
from tab where date >= '2013-06-19' 

group by game_id

Upvotes: 2

Related Questions