Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  
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
     ,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!


  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020