What I need to do is in this image. How do I do it?
This is the query I made and it is coming empty and I do not know why.
SELECT event_type, value from teste t1
Where exists (select event_type, value
from teste t2
where t2.event_type = t1.event_type
and t2.value = t1.value
group by event_type, value
having count(*) > 1)
In SQL Server you can do as follows
;with cte as
(
select event_type,
value,
[time],
dense_rank() over (partition by event_type order by [time] desc) rn
from [events]
)
select c1.event_type, c1.value - c2.value value
from cte c1
inner join cte c2
on c2.event_type = c1.event_type
and c2.rn = c1.rn + 1
where c2.rn = 2
order by 1
I tested this table and this data and it produced the expected result.
You can check SQLFiddle: link
In MySql you can do this:
select
event_type,
count_event_type,
(select value from events events_2 where events_2.event_type = events.event_type order by time desc limit 0,1) -
(select value from events events_2 where events_2.event_type = events.event_type order by time desc limit 1,1) as total2
from (
select
event_type,
count(event_type) count_event_type,
value,
time
from
events
group by event_type
) events
having count_event_type > 1;
Example working: link
Try:)
select e.event_type,
e.value - (select top 1 value from events
where event_type = e.event_type
and time <> e.time
order by time desc) as value
from events e
where e.time = (select top 1 time from events
where event_type = e.event_type
order by time desc)
order by e.event_type ASC