Archives of the TeradataForum

Message Posted: Fri, 24 Feb 2012 @ 14:43:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Fred,

One key question on the requirements for this: What happens if two rows with val=116 are within 20 rows of each other? Do yu restart the count? From what you've said I'm assuming the answer is yes.

I think the following will do the trick for you, but you have to be on TD13.x. The RESET WHEN clause won't work on TD12 or earlier.

     select id
           ,datetimestamp
           ,row_number() over(order by datetimestamp asc reset when value1 = 116) as RowFrom116
           ,value1
     from t116
     where datetimestamp >= (select min(datetimestamp)
                            from t116
                            where value1 = 116) Qualify RowFrom116 <= 20;

One possible downside to this is that there are two scans of the table - which you've said is big. That may be prohibitive for you.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>