Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 22:16:35 GMT


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


Subj:   Re: Consecutive sequence sql logic
 
From:   John Grace

Bruce,

I was thinking along the lines David Hough. My twist was to take the self join out to five and avoid the "between" (you also will not need the "count"). This technique is usually pretty fast, but as always a lot depends on the table and indexes.

select
 p1.acct, p1.phone, p2.phone, .....
from
 cust as p1
  inner join cust as p2
  on p2.phone = p1.phone+1 and
     p2.acct = p1.acct
  inner join cust as p3
  on p3.phone = p1.phone+2 and
     p3.acct = p1.acct
  inner join cust as p4
  on p4.phone = p1.phone+3 and
     p4.acct = p1.acct
  inner join cust as p5
  on p5.phone = p1.phone+4 and
     p5.acct = p1.acct

group by
 p1.acct, p1.phone, p2.phone, .....
;

John Grace
Geppetto's Workshop



     
  <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