Archives of the TeradataForum

Message Posted: Thu, 27 Aug 2009 @ 22:17:33 GMT

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

cblake wrote:

It's OLAP time again :-)

     select i.logicalKey1
     ,i.logicalKey2
     ,i.logicalKey3
     ,max(i.desiredAttribute) as chosenDesiredAttribute
     from table i
     group by 1,2,3
     qualify
        rank() over
            (partition by logicalKey1
              , logicalKey2
              , logicalKey3
             order by timeStampKey1,sequenceNbrKey1) = 1

Instead of GROUP BY you you might add i.desiredAttribute to the order by:

     select i.logicalKey1
     ,i.logicalKey2
     ,i.logicalKey3
     ,i.desiredAttribute
     from table i
     qualify
        rank() over
            (partition by logicalKey1
              , logicalKey2
              , logicalKey3
             order by timeStampKey1,sequenceNbrKey1,i.desiredAttribute) = 1

Dieter

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