Interested to Buy Any Domain ? << Click Here >> for more details...
Write a single SQL to delete duplicate records from the a
single table based on a column value. I need only Unique
records at the end of the Query.
- 13 Answers
- 59177 Views
- I also Faced
- E-Mail Answers
Answer Posted / bharath
guys rowid concept is discontinued in teradata as far as my
knowledge goes, we can always use below sql ...
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;
refer to teradata forum for more info
| Is This Answer Correct ? | 17 Yes | 5 No |
Post New Answer View All Answers
What is the purpose of joins in teradata and what are the available join types?
What is meant by a Clique?
Why is the case expression used in teradata?
Which is more efficient group by or distinct to find duplicates?
How do you do backup and recovery in teradata?
Explain some differences between mpp and smp?
What are the enhanced features in teradata v2r5 and v2r6?
What is smp and mpp platforms?
What are the joins in teradata and how many types of joins are there in teradata?
What are tpump utility limitations?
What is bteq script in teradata?
Describe the set operators in teradata.
Can we collect statistics on multiple columns?
What are the functions involved in shared information architecture?
What are the different design perspectives used in teradata?
