Select 2 latest records from Sql Server table

Question

I have a table with the following fields.

IMEI | TimeStamp | Value
123  | 1570011000| 1
123  | 1570012800| 0
123  | 1570015200| 1
123  | 1570017000| 0

I would like to take the last 2 records and make the difference between the Timestamp of the last 2 records.

I thought I’d make a select for each record:

select value, TimeStamp from myTable where TimeStamp = (select max(TimeStamp) from myTable) and value ='1' 

select value, TimeStamp from myTable where TimeStamp = (select max(TimeStamp) from myTable) and value ='0' 

But in the first select I have no return (just does not return anything and not error)

Already in the second select he returns me correctly.

How could I do that ?

Answer 1

His column of timestamp can even be considered key, since it will most likely never be repeated.

The consultation the way it is will always take the longest ((select max(TimeStamp) from myTable)), and he will always be value 0 or 1; so it gets back in just one of the queries.

Try it like this:

select value, TimeStamp 
from myTable 
where TimeStamp = (select max(TimeStamp) from myTable where value ='1')

select value, TimeStamp 
from myTable 
where TimeStamp = (select max(TimeStamp) from myTable where value ='0')

Answer 2

I’d like to take the last two records and make the difference between the Timestamp of the last 2 records.

Whereas you’ll always have it in order 0 and 1 in value, then you can do for sub-query already making the subtraction:

SELECT
((SELECT MAX(timestamp) FROM tabela_a WHERE value = 1) - 
(SELECT MAX(timestamp) FROM tabela_a WHERE value = 0))
AS diferenca

This will bring you the value of the difference already. If that’s not what you want, then your question is wrong.