Archives of the TeradataForum

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

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

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>>