MYSQL query to get records with value that increased most between two dates

Question

I have a MySQL table ScoreArchive with following fields:

ID (int), primary key

Date (date)

Score (int)

I record in the table the Score of each ID every day.

Now I wish to find the IDs that have the top score increase between, for example, 2011-04-22 and 2011-05-31.

How can I find these using a MySQL query?

Answer 1

Try something like:

select id, max(score) - min(score) as diff ... group by id order by diff desc

---

Edit (following up on the comment):

Or something like:

select id, final_scores.score - start_scores.score as diff
from (
   select id, min(date) as min_date, max(date) as max_date
   from scores
   where date between ...
   group by id
   ) as ranges
join scores as final_scores
on final_scores.date = ranges.min_date
join scores as start_scores
on start_scores.date = ranges.max_date
where ...
order by diff desc