Database
Fan

## RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Hi Expert

When i try to use "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" in

FIRST_VALUE (Col_name) OVER (PARTITION BY ACCOUNT_ID ORDER BY ACCOUNT_

ID , VALID_FROM RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI

NG) Col_name ,

LAST_VALUE (Col_name2) OVER (PARTITION BY ACCOUNT_ID

ORDER BY ACCOUNT_ID , VALID_FROM  RANGE BETWEEN UNBOUNDED PRECEDING AND

UNBOUNDED FOLLOWING) Col_name2 ,

\$

*** Failure 3706 Syntax error: Expected the word RESET or ')' after ORDER B

Y clause.

so it fails

is there's a work arround for that?

4 REPLIES
Junior Contributor

## Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

RANGE syntax is not implemented in Teradata, you need to user ROWS instead:

`FIRST_VALUE (Col_name) OVER (PARTITION BY ACCOUNT_ID ORDER BY ACCOUNT_ID , VALID_FROM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Col_name`
Fan

## Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Actully Rows does not replace range in most of cases. is there's a workarround for this.

Moh Attia

Junior Contributor

## Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Hi Moh,

for UNBOUNDED there's no difference between RANGE and ROWS :)

Otherwise a possible workaround depends on what you actually need as a result...

E.g. there is another topic Help coding on count historical event within a fix time period, greatly thanks!, which unfortunately needs RANGE and there's no workaround.

Fan

## Re: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Dieter,

The other Topic was based on the Time so extracting hour component from the eventtime helped to resolve it.

Moh,

If you can provide some sample data and what you are expecting, it would help to understand the problem.