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
- 55747 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / nunna
Query to find duplicates in a table:(Custname, Prod,
Order_amt)
select custname,count(*) from sales1 a where a.rowid > ANY
(select b.rowid from sales1 b where a.custname=b.custname
and a.prod=b.prod and a.order_amt=b.order_amt) group by
custname;
Query to delete duplicates:
delete from sales1 a where a.rowid > ANY (select b.rowid
from sales1 b where a.custname=b.custname and a.prod=b.prod
and a.order_amt=b.order_amt);
| Is This Answer Correct ? | 7 Yes | 15 No |
Answer / manny
One need have atleast a unique column such as timestamp col
(and assumption is to keep lowest tmpstmp) OR some key col
say IPID (again keep lowest value)..
One determined - Have a nested Select on all rows (except
that key col) with group by rest of the columns + having
count(*) > 0 + aggreate MIN(key_col).
Now said that, have another outer SEL on all columsn &
do a inner join with above nested Sel .. WHERE outer
key_col <> MIN value of nested SEL..
See if it works..
| Is This Answer Correct ? | 5 Yes | 16 No |
Answer / milind
Nested query method might be required in other databases
how ever in TD we don’t need to follow such a difficult way
to just find out the unique rows.
In TD we have functions like Rank () and Rownum() in the
combination of Qualify, helps you to select out the rows
which you wants to delete.
you can add a condition like ‘Where Rank() > 1’
| Is This Answer Correct ? | 3 Yes | 16 No |
What are the scenarios in which full table scans occurs?
Hi frnds this question is asked at IBM One query is there when we dont collect stastics on it,exlapin will show us LOW CONFIDENCE,When we collect stastics it shows HIGH CONFIDENCE.WHAT IS THE INTERNAL ARCHITECTURE ACTUALLY GOING ON ?????ASKED ON 23-08-2012 THANKS IN ADVANCE
What is the difference between global temporary tables and volatile temporary tables?
what are the roles and responsibilities of teradata DBA ?
tomorrow i have interview in infosys.can someone pour any suggestions or any interview questions. thank you
Hello Frndz, I have a table named product as shown below: product_id product_name 1 AAA 1 BBB 1 CCC 2 PPP 2 QQQ 2 RRR Now my output should be: product_id product_name_1 product_name_2 product_name_3 1 AAA BBB CCC 2 PPP QQQ RRR
If the PMON is not working then how do you manage and monitor all processes, resources and sessions etc.
What are the uses of client software involved in teradata?
Hi send me the Teradata dumps to my id rajeshanantha@yahoo.co.in Thanks Rajesh. A
Difference between Teradata V2R5 and Teradata V12 versions?
where we can use the delimiter in mload? pls let me know
in a BTEQ we have 2 insert 2 del 2 update statment. when the BTEQ is restarted i need to run the BTEQ from after DEL statment(means no need to run the insert & del stat).what is the logic for the above requirement?
Teradata 
