CODEWITHSUNDEEP
sqloracle-database
MR JACKPOT
MR JACKPOT

Reputation: 214

SQL get the most recent duplicate entry with timestamp

I have the following table:

ID          DESCRIPTION                 TIMESTAMP
1            RECEIVER                    00:10:00
1            SENDER                      00:08:00
1            EXECUTOR                    00:05:00
1            SENDER                      00:03:00

How do I get the most recent 'SENDER' description and find the time difference with another description using my timestamps?

I want to find the time difference between SENDER and EXECUTOR, but I am getting weird results as its picking up BOTH SENDER entries.

Thanks Adam

Upvotes: 1

Views: 73

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You may use such a mechanish by using lag and row_number functions :

select id, timestamp_diff
  from
(
  with t(ID,DESCRIPTION,TIMESTAMP) as
  (
   select 1,'RECEIVER',to_timestamp('00:10:00','HH24:MI:SS') from dual union all
   select 1,'SENDER',to_timestamp('00:08:00','HH24:MI:SS') from dual union all
   select 1,'EXECUTOR',to_timestamp('00:05:00','HH24:MI:SS') from dual union all
   select 1,'SENDER',to_timestamp('00:03:00','HH24:MI:SS') from dual 
  )
   select t.id, 
          t.timestamp - lag(t.timestamp) over (order by t.timestamp desc) as timestamp_diff,
          row_number() over (order by t.timestamp) as rn 
     from t   
    where t.description = 'SENDER'
)
where rn = 1;

 ID    TIMESTAMP_DIFF
 -- --------------------
 1  -000000000 00:05:00

For more than one ID consider using the below one :

select id , max(timestamp_diff) as timestamp_diff
  from
  (
    with t(ID,DESCRIPTION,TIMESTAMP) as
    (
     select 1,'RECEIVER',to_timestamp('00:10:00','HH24:MI:SS') from dual union all
     select 1,'SENDER',to_timestamp('00:08:00','HH24:MI:SS') from dual union all
     select 1,'EXECUTOR',to_timestamp('00:05:00','HH24:MI:SS') from dual union all
     select 1,'SENDER',to_timestamp('00:03:00','HH24:MI:SS') from dual union all
     select 2,'SENDER',to_timestamp('00:06:00','HH24:MI:SS') from dual union all
     select 2,'SENDER',to_timestamp('00:02:00','HH24:MI:SS') from dual 
    )
     select t.id, 
            t.timestamp - lag(t.timestamp) over 
            (partition by t.id order by t.id,t.timestamp desc) as timestamp_diff,
            row_number() over (order by t.id,t.timestamp) as rn,
            t.description 
       from t   
      where t.description = 'SENDER'
  )  
  group by id, description;

 ID    TIMESTAMP_DIFF
 -- --------------------
 1  -000000000 00:05:00
 2  -000000000 00:04:00

Upvotes: 2

TheWildHealer
TheWildHealer

Reputation: 1626

SELECT
    t1.timestamp - t2.timestamp
from
    (SELECT 
        timestamp 
    FROM 
        table 
    WHERE 
        description='SENDER' 
    ORDER BY timestamp DESC LIMIT 1) t1,
    table t2
WHERE
    t2.description = 'your_description'

Upvotes: 2

Related Questions