Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 09 Oct 2000 @ 14:46:14 GMT


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


Subj:   Re: V2R3 SQL Documentation - Rank Function
 
From:   Lee Brown

Barbara,

It has been a while since I have used it, but I think what it means by the 'sort_expression_list' is the combination of values you wish to rank and the order the values should be judged (I hope that makes sense!)

e.g. RANK (SALES_VALUE DESC, COST_VALUE ASC) would rank the most profitable transactions first, but it wouldn't be all that useful in this state, because 1) their is no descriptive values selected with it and 2) you would have an awful lot of numbers returned.

If you wish to rank the most profitable department you would have to use a derived table, also if you wished to restrict the returned answer set you need to make use of the QUALIFY statement.

     e.g.
     SELECT
             DEPT_NAME, RANK ( SALES_VALUE DESC, COST_VALUE ASC ) FROM
             (SELECT DEPT_NAME, SUM(SALES_VALUE), SUM(COST_VALUE)
              FROM
                     SALES
              GROUP BY 1
              )
     QUALIFY RANK ( SALES_VALUE DESC, COST_VALUE ASC) <= 10

Hope this helps


Regards

Lee



     
  <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: 27 Dec 2016