Archives of the TeradataForum

Message Posted: Thu, 06 May 2004 @ 15:08:18 GMT

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

The RANK or CSUM would not be used in a DELETE statement, but rather in an insert-select to an empty table, eg.

     INSERT INTO nodupes_table ( all_columns )
     SELECT all_columns
     FROM dupes_table
     QUALIFY ...

where the QUALIFY ... can be (depending on your version):

     /* V2R5 syntax */
     QUALIFY ROW_NUMBER() OVER (PARTITION BY all_columns ORDER BY all_columns) = 1

     /* or V2R4 and higher equivalent functionality */
     GROUP BY all_columns
     QUALIFY CSUM(1, all_columns ) = 1

     /* or, alternative OLAP SUM V2R4 and higher syntax */
     QUALIFY SUM(1) OVER (PARTITION BY all_columns ORDER BY all_columns ROWS
     UNBOUNDED PRECEDING ) = 1

I do think an insert-select into a set table would be a cleaner process (don't know about runtime, though):

     INSERT INTO nodupes_set_table ( all_columns )
     SELECT all_columns
     FROM    dupes_table;

Donald Maxwell

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