Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Easy way to get the latest updated row in a query
 
From:   cblake

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016