Return the difference between the last two records

Asked

Viewed 183 times

2

What I need to do is in this image. How I do?

inserir a descrição da imagem aqui

This is the query I did and it’s coming empty and I don’t 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)
mysql sql sql-server
  • 1

    just read, hehe joke, the right would be you translate does not think? :)

  • The time and why I posted everything and only have 1:20 to finish and are three questions. What is asked is to take the repeated values make a sub with the first value and the last.

  • You would have better result posting in the OS in English, since you can not translate.

3 answers

1

In Mysql you can do so:

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;

Working example: http://sqlfiddle.com/#! 9/2180e/16

  • In Sql Server as I replace the limit

  • SELECT TOP 1 is the same as LIMIT 1

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 with this table and these data and produced the expected result.

You can check the Sqlfiddle: http://sqlfiddle.com/#! 3/f4f05/3

  • Invalid cte2 object name. This is the error when I run

  • It’s not like that. I’ll post it in English. The answer should be: 2 <<< -5 and 3 <<< 4

  • The result is empty. I made a query with Having and it is also empty. I will edit and post the query. I don’t know why it is like this.

0

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

Sqlfiddle Demo

Browser other questions tagged mysql sql sql-server

You are not signed in. Login or sign up in order to post.