User talk:Kymara: Difference between revisions

@old := (select points from halloffame_archive_recent r1 where r1.charname = name and fametype = 'M' and day < date_sub(enddate, interval ndays day) order by day desc limit 1) old,

-- now put it all together in one super amazing query that runs in about 35 seconds!! wow super amazing hey!!

-- (it would be way slower if the function call was not done 'outside' the scores table as the scores table uses a 'having' so is only small after it's completed. incase you wondered why I did that)

select charname, difference as totalscore, getMaxMazeScore(charname, date(now()), 14) as maxscore

from

(select

charname,

points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2 where fametype = 'M' and day = date(now())

having difference >0

order by difference desc)

scores;

-- output is name, total score, max score, for all participants