Search code examples
sqlgreatest-n-per-group

How to Select a Row Based on Value From previous matched row?

Suppose the following table:

Action,      Action_Timestamp
act_b        1
act_a        2
act_x        3
act_b        4
act_c        6
act_b        7
act_c        8

What I'd like to find whether there is such sequence of actions (with actions between them): 

`act_a -> act_b -> act_c` where `act_a_timestamp < act_b_timestamp < act_b_timestamp` (aka first occurrence of each event)

So the result should look like this:

Action,      Action_Timestamp
act_a        2
act_b        4
act_c        6

Could you please help me in writing SQL query to obtain result shown above?

P.s. Limits are not available in my RDBMS

Solution

  • You didn't state your DBMS, so this is standard SQL:

    select action, action_timestamp
from (
  select action, action_timestamp, 
         row_number() over (partition by action order by action_timestamp) as rn
  from the_table
) t
where rn = 1;