Archives of the TeradataForum

Message Posted: Thu, 27 Aug 2009 @ 22:27:00 GMT

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

Chris -

     Select i.WhateverFieldsYouWant
     From table i
     Qualify timeStampKey1 = max(timeStampKey1) over (partition by
     i.logicalKey1, i.logicalKey2, i.logicalKey3)

Your query also has sequenceNbrKey1, which I'm guessing is there in case there are multiple rows with the same logicalKey1-3 and timeStampKey1. The query above will get all rows with the max timestamp if there are more than one. If you just want one, use sequenceNbrKey1 in the qualify instead.

Your query is not wrong, although it requires that the ETL is structured so that the max timestamp for a compound key will also always have the max sequence number, which may or may not be the case. And since the output of the join should be one row, no need for the max on the chosen attribute.

Dave

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