Re: Selecting a derived field

[Roland Bouman]

select c.period
, c.player
, c.wins
, if(
c.period = p.period
, c.wins
, c.wins-p.wins
) as diff
from playerstats c
inner join playerstats p
on c.player = p.player
and c.period >= p.period
and p.period = (
select coalesce(max(pp.period),c.period)
from playerstats pp
where pp.player = c.player
and pp.period < c.period)
where c.period = {put the current period here}
order by diff desc
limit 1;

btw, what do you mean by "derived field"?

You could probably improve performance if you could be sure that a player never skips a period (this query will yield consistent results if a player does skip one or more periods)

Just anohter thought, you are storing cumulative data, isnt it a good idea to calculate this difference of wins at the moment you are calculating or inserting the cumulative wins?