Archives of the TeradataForum
Message Posted: Thu, 27 Aug 2009 @ 21:43:48 GMT
I had a series of tables that have multiple rows for each of what a user would consider the key where the addition of a timestamp makes the row unique.
What I'm looking for is the easiest / fastest way to pull the row with the most recent timestamp number.
Here is the only way I can think of to do this, but I thought I saw a query that used some sort of short cut to do the same thing,
Something along the lines of:
where timeStampKey1 = max(timeStampKey1)
I cannot find the query that had this, and this doesn't work (nor should it in my opinion), But I thought maybe there was a short cut for this sort of thing.
Does anyone have any ideas?
select i.logicalKey1 ,i.logicalKey2 ,i.logicalKey3 ,max(i.desiredAttribute) as chosenDesiredAttribute from table i inner join ( select logicalKey1 , logicalKey2 , logicalKey3 , max(timeStampKey1) as maxTimeStamp , max(sequenceNbrKey1) as maxSequenceNbr from table group by 1,2,3 ) m on m.logicalKey1 = i.logicalKey1 and m.logicalKey2 = i.logicalKey2 and m.logicalKey3 = i.logicalKey3 and m.maxTimeStamp = i.timeStampKey1 and m.maxSequenceKey1 = i.sequenceNbrKey1 group by 1,2,3
Thanks in advance!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|