Archives of the TeradataForum

Message Posted: Wed, 06 Feb 2008 @ 21:48:52 GMT

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

In my experience joining a table to itself doesn't perform as well as using ordered analytics if you are dealing with tables with many millions of rows. Here is another alternative for your situation.

     select id,
             name,
             amount as low_amount,
             max(amount) over(partition by name
                                  order by amount desc
                                   rows between 1 preceding
                                            and 1 preceding) as high_amount,
             rate
        from your_table

Hope this helps.

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