Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

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


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,


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019