Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 11 Aug 2005 @ 11:19:10 GMT


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


Subj:   Re: Help need on Tricky SQL
 
From:   Gaitonde, Amol A

Try this SQL

     SELECT A.PKEY, A.Applied, COALESCE (B.applied ,( '9999-12-31' (Date, format 'YYYY-MM-DD')))
     FROM
     (
     SELECT PKey PKEY, applied applied,RANK () OVER  (PARTITION BY PKEY
     ORDER BY   applied ASC ) ORD
     FROM SQL1) A LEFT OUTER JOIN (
     SELECT PKey PKEY, applied applied,RANK () OVER  (PARTITION BY PKEY
     ORDER BY   applied ASC ) ORD
     FROM SQL1)B
     ON  A.PKEY = B.PKEY
     AND A.ORD = B.ORD-1
     order by 1,2,3

But this Joins on Derived values and Performance may not be good.

Thanks.



     
  <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: 15 Jun 2023