Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2009 @ 17:18:35 GMT

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

I'm curious about how RANK(), ROW_NUMBER() and MAX() would perform.

In theory you don't have to sort to get MAX(), you only have to read data, not sort it and write it back to disk. Does Teradata physically sort to get MAX() OVER()?

If it doesn't (and leaving aside rules for tie-breaks), would it be better to use

     SELECT * FROM lineitem
     QUALIFY
             l_quantity = MAX(l_quantity) OVER (PARTITION BY l_partkey, l_shipdate);

Instead of

     SELECT * FROM lineitem
     QUALIFY
         RANK() OVER
             (PARTITION BY l_partkey, l_shipdate
              ORDER BY l_quantity DESC) = 1;

Dave

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