Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Too much time spent on aggregate function
 
From:   Curley, David

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023