Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 28 Aug 2009 @ 09:08:49 GMT

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

Subj:   Re: Easy way to get the latest updated row in a query
From:   Victor Sokovin

Dave, I have read your latest posting again and then I noticed that it was your second posting in the thread so both of them together have clarified your ideas. I am still struggling with the syntax of the CASTs in your query but I think I have understood your main idea and it does work under the conditions you have added in your first posting. The extra condition is indeed an important addition to what was stated by the OP: two separate column-level maxima on timeStampKey1 and sequenceNbrKey1 are realized on the same row(s) within each partition of the logical keys logicalKey1, logicalKey2, logicalKey3. You also optimally use the fact that it is again MAX (MIN or SUM would not work with this method) that is calculated for desiredAttribute and the timeStampKey1 is well-structured and can be used for concatenation.

Dieter, this is also the extra condition that you seem to be using without stating it. In general, we don't know how column-level maxima are distributed across the rows in partitions. It can happen that MAX(timeStampKey1) and MAX(sequenceNbrKey1) can sit on different ones and the JOIN in the OP's query will return no rows. A more careful rewrite of that query in OLAP terms should probably include two QUALIFY clauses. Agreed?


  <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: 15 Jun 2023