Archives of the TeradataForum

Message Posted: Thu, 07 Feb 2008 @ 14:04:01 GMT

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

Hello Dieter:

Evidently you have a lot more time to test other people's code. That is great that you can be so helpful.

After seeing the results that you point out, a simple adjustment of adding .00001 to the amount in the qualify (so insignificant that it does not change the actual data) provides the desired results because it would never be equal to the high or low amounts in the BETWEEN:

     select rateid,
               name,
               rate
       from rates
     qualify .1653+.00001 between amount and
          max(amount) over(partition by name
                                    order by amount
                                     rows between 0 preceding
                                              and 1 following)

I think the bigger point is that it requires OLAP (more than one possible) to accomplish this and so many tasks that were previously more difficult.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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