Find last match in a column return to value in the next row

Database
CC
Fan

Find last match in a column return to value in the next row

Hi all,

 

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
... ... ...
5 REPLIES
Teradata Employee

Re: Find last match in a column return to value in the next row

One approach would be:

select ID, Time, Event
from ...
Qualify min(Event) over(rows between 1 preceding AND 1 preceding)  = 44
Teradata Employee

Re: Find last match in a column return to value in the next row


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.

CC
Fan

Re: Find last match in a column return to value in the next row

Hi Waldar,

 

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.

 

CC
Fan

Re: Find last match in a column return to value in the next row

Hi GJ,

 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.

Teradata Employee

Re: Find last match in a column return to value in the next row

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?!?