Archives of the TeradataForum

Message Posted: Thu, 27 Aug 2009 @ 21:43:48 GMT

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

Hello everyone,

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!

Chris

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