MySQL: Change the value of a column between the oldest and newest date in the table.
The following table in MySQL:
id| value | date
There are multiple records for the same id for different dates, I want each "id" to calculate the difference in the column value between the records with the old and newest "date" in my table.
In other words, display something like (remember the value is the Value column):
id | Most Recent Value - Oldest Value
Any help / advice would be more than welcome! So far I have tried to self-join the table 2 times to take records for both first and last date using min () and max () but no luck, I get NULL as "value" for min () dates ...
SELECT t.id, (t2.value - t3.value) AS Difference
FROM table t
LEFT JOIN
(SELECT t2.id, t2.value, MAX(t2.date) as TopDate
FROM table t2
GROUP BY t2.id) AS LatestValue
ON LatestValue.TopDate = t.date AND LatestValue.id = t.id
LEFT JOIN
(SELECT t3.id, t3.value, MIN(t3.date) as BotDate
FROM table t3
GROUP BY t3.id) AS FirstValue
ON FirstValue.BotDate = t.date AND FirstValue.id = t.id
GROUP BY t.id;
source to share
Try the following:
select t1.id, t1.`value` - t2.`value` as res from
(
select t.id, `value` from t
inner join (select id, max(`date`) dt from t group by id) mx
on t.id = mx.id and t.`date` = mx.dt
)t1
inner join (
select t.id, `value` from t
inner join (select id, min(`date`) dt from t group by id) mn
on t.id = mn.id and t.`date` = mn.dt
) t2
on t1.id = t2.id
Side note: do not use words like value
, date
as column names
source to share
In your internal requests, the path the method was used was in error. It won't get you the right meaning. You don't need a group in your external queries either. If you want to do it the way you did, then the correct query is: -
SELECT t.id, (LatestValue.value - FirstValue.value) AS Difference
FROM table t
LEFT JOIN
(
SELECT id,value, date as TopDate
FROM TABLE a
INNER JOIN
(
SELECT t2.id, MAX(t2.date) as TopDate
FROM table t2
GROUP BY t2.id
) b
ON a.id=b.id and a.date=b.TopDate
) LatestValue
ON LatestValue.TopDate = t.date AND LatestValue.id = t.id
LEFT JOIN
(
SELECT id,value, date as BotDate
FROM table a
INNER JOIN
(
SELECT t3.id,MIN(t3.date) as BotDate
FROM table t3
GROUP BY t3.id
) b
ON a.id=b.id and a.date=b.BotDate
) FirstValue
ON FirstValue.BotDate = t.date AND FirstValue.id = t.id;
Let me know if this works or if you have any questions.
source to share
Here's a solution that only scans the table once. It uses MySQL variables to fix the flaws of the window functions.
select id
, first_val - last_val
from (
select case when id <> @id and @id <> -1 then @id end as id
, case when id <> @id then @first_val end as first_val
, case when id <> @id then @last_val end as last_val
, @first_val := case when id <> @id then `value` else @first_val end
, @last_val := `value`
, @id := id
from (
select *
from (
select *
from YourTable
order by
id desc
, date
) sorted
union all
select -1
, null
, null
) with_closer
cross join (select @id := -1, @first_val := 0, @last_val := 0) r
) calc
where id is not null
This should work well, especially with an index on (id desc, date, value)
.
source to share