Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 09 Jul 2004 @ 15:56:24 GMT


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


Subj:   Re: Rank Fn in SubQuery
 
From:   Jason Fortenberry

Bhupesh,

It sounds like the basic question you are asking is to get the top value for each of your col2 and col3 sets, resetting by col2. Then you could qualify on which col2 value you want.

Perhaps something like the following would work, well at least for your sample data it does. I am testing this on V2R5.0.3. This will add a column to your subquery, I trust that is ok:

     select * from
     (select a.col1, a.col2, a.col3,
     RANK() OVER (PARTITION BY a.col2 ORDER BY a.col2 desc, a.col3 asc) as
     RankS from dropme a
     qualify RankS = 1) a
     where a.col2 = 2;

Good luck,

Jason



     
  <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