Recursive Query help

General
Fan

Recursive Query help

Hi,

I am new to Teradata and I am working on query for which I greatly appreciate any help. I have a following table (after ordering by col1 and col2) 

Col1        col2             col3        

Alpha      10:00          activity typ 1

Alpha       10:00:30    activity typ 2

Alpha       10:00:40     activity typ 3

Alpha       11:00           activity typ 1

Alpha        11:00:20    activity typ 4

Now the understanding is that when ever col3 hits 'activity typ 3' or 'activity typ 4' the process ends and they become the summary typs for the rest of ther above columns.  I need to write a query so that the result set would like

Col1        col2             col3        

Alpha      10:00          activity typ 3

Alpha       10:00:30    activity typ 3

Alpha       10:00:40     activity typ 3

Alpha       11:00           activity typ 4

Alpha        11:00:20    activity typ 4

Tried to use partition by and row number functions but couldn't get far. 

Tags (2)
2 REPLIES
Senior Apprentice

Re: Recursive Query help

There's no need for recursion, you just have to sort the data descending and then use RESET WHEN:

SELECT 
col1, col2,
MIN(CASE WHEN col3 IN ('activity typ 3', 'activity typ 4') THEN col3 END)
OVER (PARTITION BY col1
ORDER BY col2 DESC
RESET WHEN col3 IN ('activity typ 3', 'activity typ 4')
ROWS UNBOUNDED PRECEDING)
FROM tab
ORDER BY col2
Fan

Re: Recursive Query help

Thanks so  much Dieter. The query worked as expected.