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
- 55725 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 are the joins in teradata and how many types of joins are there in teradata?
Give the sizes of SMALLINT, BYTEINT and INTEGER.
how do we write scripts in unix how to execute scripts in real time anybody please needfull or give me number i will cal u
What is node? How many nodes and amps used in your previous project?
Explain some differences between mpp and smp?
What are normalization, first normal form, second normal form and third normal form?
Briefly explain each of the following terms related to relational database management system (rdbms) – database, tables, columns, row, primary key and foreign key.
Can any one explain me the difference between BTEQ and MLOAD,TUMP. All canbe used for same purpose but still differnt methods. why ?
Highlight the advantages of PPI(Partition Primary Index).
What are the various indexes in teradata? How to use them? Why are they preferred?
Explain and compare pros and cons of start schemas?
What tools would you use for that?
Explain the parallel data extension in teradata?
What is primary index and secondary index?
List the logical and conditional operators that are used with teradata along with their meanings?
