Archives of the TeradataForum

Message Posted: Thu, 16 Apr 2009 @ 16:26:30 GMT

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

     Select count(*)
     From
     (select 1 as dummyfield
      from MyTable
     Qualify count(*) over (partition by PIfield1, PIfield2...,PIfieldN) >
     1)
     x

That will get the count of all rows involved in duplicate PIs. If the table is big enough, it's probably somewhat faster to do it this way rather than qualify on row_number() over an ordered partition, since this way doesn't have to sort.

If you need to know how may distinct duplicate PI entries you have, try

     Select count(*)
      from
     (Select PIfield1, PIfield2...,PIfieldN
      from MyTable
     Group by 1,2...,N
     Having count(*) > 1) x

Or you can get both sets of info in one query:

     Select count(*) as CountOfDupePI, sum(DupePIRows) as CountOfDupePIRows
      from
     (Select PIfield1, PIfield2...,PIfieldN, count(*) as DupePIRows
      from MyTable
     Group by 1,2...,N
     Having count(*) > 1) x

If you want the rows themselves:

     select *
      from MyTable
     Qualify count(*) over (partition by PIfield1, PIfield2...,PIfieldN) > 1

Dave

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