Thought it will be more helpful if I post it here :) many thanks for your time and help in advance!
I have a table below, I ordered it by time. I want to find the event happened after the last event '44'. Could you please help? Thanks!
ID Time Event A 00:10:00 11 A 00:10:11 44 A 00:10:13 22 A 00:10:00 11 A 00:10:11 44 A 00:10:13 33 A 00:10:13 22 B 01:10:00 ? B 01:10:11 44 B 01:10:13 11 B 01:10:00 44 B 01:10:11 ? B 01:10:13 22 ... ... ...
Desired result: ID Time Event A 00:10:13 33 B 01:10:13 22 ... ... ...
One approach would be:
select ID, Time, Event from ... Qualify min(Event) over(rows between 1 preceding AND 1 preceding) = 44
Qualify min(Event) over(rows between 1 preceding AND 1 preceding) = 44
Very neat, it lacks a bit of semantics for OP :
min(Event) over(partition by Id order by Col_time rows between 1 preceding and 1 preceding) = 44
@CC, please not that in 16.x you can use the LAG function :
lag(Event) over(partition by Id order by Col_time asc) = 44
Also from your dataset, you have to specify some rules for multiples candidates : you have several rows with Event 44 at same time per Id.
Keep in mind that TIME is a reserved word, that you shouldn't use as a column name inside your database. I guess it's just for the example here.
Thanks for the detail. Time is different for each ID.
I am new to TD SQL, the command you have here will return multi-rows for each ID. I need only one row for each ID - the row next to the last '44' event.
Could you please help? Thanks in advance.
Thanks for the reply. The command you have here will return multi-rows for each ID. I need only one row for each ID - the row next to the last '44' event... Any thoughts? Thanks.
There is something missing here - the sequence of events within an ID. Time is not sufficient because for example ID 'A' has two 22's and a 33 following the 44 in time sequence. Which is the one you are looking for?!?