CODEWITHSUNDEEP
mysql
Timothée HENRY
Timothée HENRY

Reputation: 14604

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

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?

Upvotes: 3

Views: 373

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

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

Upvotes: 4

Daniel Casserly
Daniel Casserly

Reputation: 3500

SELECT score FROM ScoreArchive WHERE date BETWEEN 2011-04-22 AND 2011-05-31 ORDER BY score DESC;

That's how i would do it in pgsql i am guessing that mysql is the same

Upvotes: 0

Related Questions