Archives of the TeradataForum

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

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

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>>