 |
 |
Archives of the TeradataForum
Message Posted: Fri, 28 Aug 2009 @ 09:08:49 GMT
| 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?
Victor
| |
