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
- 59971 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / vinay sir(datawarehousing tech
Yuva,Nice to see your answers.I hope we can't use "Ordered
Analytical Queries in Where Clause".So first answer may not
be correct.Please check and let us know.
2nd way is one of the way where we are using intermediate
table to delete duplicates.
Any way thank you much Yuva for your nice postings.
| Is This Answer Correct ? | 9 Yes | 1 No |
Answer / yuvaevergreen
Hi Vinay,
Thanks ya for the catch and appre...
Hi guys,
Please ignore the first approach using the delete statement. It is wrong.
| Is This Answer Correct ? | 5 Yes | 0 No |
Answer / yuvaevergreen
If atleast one column is distinct, we can delete using
delete statement.
EMPLOYEE TABLE:
EMPNO EMPNAME DEPT
1 YUVA SCI
2 YUVA SCI
DELETE FROM EMPLOYEE WHERE
(EMPNO, EMPNAME,DEPT)
NOT IN
(SELECT EMPNO, EMPNAME,DEPT FROM EMPLOYEE
QUALIFY ROW_NUMBER() OVER
(PARTITION BY EMPNO
ORDER BY EMPNO,EMPNAME,DEPT ASC ) = 1 );
If all the columns are same, then create and drop would be used.
EMPNO EMPNAME DEPT
1 YUVA SCI
1 YUVA SCI
CREATE EMP_NEW AS EMP WITH NO DATA;
INSERT INTO EMP_NEW
SELECT EMPNO, EMPNAME,DEPT FROM EMPLOYEE
QUALIFY ROW_NUMBER() OVER
(PARTITION BY EMPNO, EMPNAME,DEPT
ORDER BY EMPNO,EMPNAME,DEPT ASC ) = 1;
DROP TABLE EMP;
RENAME EMP_NEW TO EMP;
| Is This Answer Correct ? | 9 Yes | 6 No |
Answer / jaya
/********maximum salary of each dept*************/
sel * from vik_emp where (salary, dept_id) in (sel max
(salary), dept_id from vik_emp group by dept_id);
sel emp_id, emp_name, a.salary, a.dept_id from vik_emp a
inner join
(sel max(salary) as salary, dept_id from vik_emp group by
dept_id) b
on
a.salary=b.salary
and
a.dept_id=b.dept_id;
/*********top 3 salary of each dept*********/
sel distinct
emp_name,
salary,
Dept_id,
rank() over (partition by dept_id order by salary desc )
rk ,
row_number() over (partition by dept_id order by salary
desc )rn
from vik_emp
order by dept_id, rk, rn
QUALIFY rn <= 3;
/*****cutomer having only one type of account******/
sel cust_nm, acctyp from cust where cust_nm not in
(sel a.cust_nm as cust_nm from cust a
inner join cust b on
a.cust_nm= b.cust_nm
and
a.acctyp <> b.acctyp);
/************coustomer with types of acc they
have***************/
select cust_nm, max(acctyp1), max(acctyp2)
--, max(srv_need3)
from
(
select cust_nm,
case when acctyp = 'saving' then acctyp else null end as
acctyp1,
case when acctyp = 'credit' then acctyp else null end as
acctyp2
--,case when srv_need = 30 then srv_need else null end as
srv_need3
from
cust
)a
group by 1;
/* How to Extract Middle name from the Full name */
select emp_name,
index(emp_name,' ') as a,
substr(emp_name,a+1) as rest_name,
substr(rest_name,1,index(rest_name,' ')) as Middle_Name
from vik_emp;
select emp_name,
--index(emp_name,' ') as a,
substr(emp_name,index(emp_name,' ') +1) as rest_name,
substr(rest_name,1,index(rest_name,' ')) as Middle_Name
from vik_emp;
| Is This Answer Correct ? | 2 Yes | 1 No |
Answer / tdguy
creating a new table would be the best option, if all the
field values are same, as far as i know.
CREATE NEW_TABLE AS OLD_TABLE WITH NO DATA;
INSERT INTO NEW_TABLE
SELECT COLUMN1, COLUMN2,COLUMN3 FROM OLD_TABLE
QUALIFY ROW_NUMBER() OVER
(PARTITION BY COLUMN1, COLUMN2,COLUMN3
ORDER BY COLUMN1, COLUMN2,COLUMN3 ASC ) = 1;
DROP TABLE OLD_TABLE;
RENAME NEW_TABLE TO OLD_TABLE;
| Is This Answer Correct ? | 1 Yes | 2 No |
Answer / joy
/* GENERAL*/
SELECT EMP_ID||''||AGE FROM EMP;
SELECT POSITION('A' IN NAME) FROM EMP;
SELECT INDEX(NAME,'SA') FROM EMP;
SELECT LOWER(NAME) FROM EMP;
SELECT UPPER(NAME) FROM EMP;
SELECT SUBSTR(name,2) FROM EMP;
/* TRIM */
SELECT TRIM(TRAILING 9 FROM AGE ) AS VIK FROM EMP;
SELECT TRIM(TRAILING '2' FROM AGE ) AS VIK FROM EMP;
SELECT TRIM(LEADING ' 8' FROM AGE ) AS VIK FROM EMP;
SELECT TRIM(BOTH '2' FROM (TRIM(LEADING ' ' FROM AGE )))
AS VIK FROM EMP;
SELECT TRIM(BOTH 'A' FROM AGE) AS VIK FROM EMP;
SELECT TRIM(AGE) FROM EMP;
SELECT TRIM(LEADING ' ' FROM AGE) FROM EMP;
SELECT TRIM(AGE) || NAME FROM EMP;
/* LENGTH */
SELECT CHARACTER_LENGTH(TRIM(NAME)) FROM EMP;
SELECT CHARACTERS(NAME) FROM EMP;
SELECT OCTET_LENGTH(NAME) FROM EMP;
/*DIRECTLY TAKE POSITION*/
SELECT
SUBSTR (NAME,
POSITION('A' IN NAME ))
FROM EMP;
/*REMOVE SPACE*/
SELECT
SUBSTR (
TRIM( NAME),2)
FROM EMP;
SELECT SUBSTR(NAME,2) FROM EMP;
/* SUM WITH PARTITION BY AGE*/
select name ,age, salary, sum(salary) over (partition by
age order by DOJ ) from emp ;
/*CUMULATIVE SUM WITH PARTITION BY AGE*/
select name,age, salary, sum(salary) over (partition by age
order by DOJ rows unbounded preceding ) from emp ;
/*CUMULATIVE SUM */
select name,age,salary, sum(salary) over ( order by DOJ
rows unbounded preceding ) as casum from emp ;
/* AVG WITH PARTITION BY AGE*/
select name ,age, salary, AVG(salary) over (partition by
age order by DOJ ) from emp ;
/*CUMULATIVE AVG WITH PARTITION BY AGE*/
select name,age, salary, AVG(salary) over (partition by age
order by DOJ rows unbounded preceding ) from emp ;
/*CUMULATIVE AVG*/
select name,age,salary, AVG(salary) over ( order by DOJ
rows unbounded preceding ) as casum from emp ;
/*Rank*/
SELECT emp_id,name,salary,age,
rank() over ( partition by name order by salary desc ) as
ranks
from emp ;
/*Rank with Order by*/
SELECT emp_id,name,salary,age,
rank() over ( order by salary desc ) as ranks
from emp ;
/*Rank With Qualify*/
SELECT emp_id,name,salary,age,
rank() over ( partition by name order by salary desc ) as
ranks
from emp
qualify ranks <=2;
/*ROw_number*/
SELECT emp_id,name,salary,age,
ROW_NUMBER() over ( partition by name order by salary
desc ) as RowNUMBER
from emp;
/*ROw_number with only order by*/
SELECT emp_id,name,salary,age,
ROW_NUMBER() over ( order by salary desc ) as RowNUMBER
from emp
/*ROw_number with Qualify*/
SELECT emp_id,name,salary,age,
ROW_NUMBER() over ( partition by name order by salary
desc ) as RowNUMBER
from emp
qualify rownumber <=2;
| Is This Answer Correct ? | 2 Yes | 3 No |
The following example shows how to create table and insert
data without duplicate rows from existing table:
CREATE TABLE NODUPS AS
(
SELECT * FROM Departments
UNION
SELECT * FROM Departments
)
WITH DATA;
| Is This Answer Correct ? | 10 Yes | 12 No |
Answer / ankal
Hi guys,
As per my knowledge i am expecting the following ans.
1.If u want delete duplicates at runtime means
output time it shows unique values but not delete
from table.
The following query.
=> Select [all columns] from tablename group by [all columns];
(OR)
2.If u want delete duplicate values from total table.
The following query.
The table having duplicates means it is multiset table so,
=> create set table nodup_table as dup_table with data;
If u want the table name as same do like this.
=> Next just drop table dup_table;
=> Finally rename table nodup_table to dup_table;
| Is This Answer Correct ? | 1 Yes | 6 No |
Answer / guest
Hope this will help,
DELETE FROM EMPLOYEE
WHERE (EMP_ID,EMP_NAME) IN
(SELECT EMP_ID,EMP_NAME FROM
(SELECT EMP_ID,EMP_NAME ,ROW_NUMBER() OVER (PARTITION BY
EMP_ID,EMP_NAME ORDER BY EMP_ID,EMP_NAME) AS FLG
FROM EMPLOYEE) A
WHERE
A.FLG<>1)
| Is This Answer Correct ? | 4 Yes | 10 No |
In your project what type of issues and errors you facing in Teradata
if a error occured in FASTLOAD is the fastload job stops?
Can we have two time dimensions in a schema(either star or snow flake)? For ex if we want joining date of employee and if we want today's sales with time whether can we have two time dimensions for accommodating above tasks?
i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 90 2 bbb 60 80 50 i want to execute every student highest mark in which subject my o/p like below studentid studentname sub3 sub2 1 aaa 90 2 bbb 80
in ur table contains the coloumns like deptno,sal empname then i want output as deptno subtotalofdept totalsal 10 3700 3700 20 3400 7100 like that
Define views in teradata with general syntax.
Can any one explain me the difference between BTEQ and MLOAD,TUMP. All canbe used for same purpose but still differnt methods. why ?
how to delete duplicate records in multi set table without using any tables
2 Answers Accenture, Nest, South Nests Software Solution, Spiro Solutions,
What is meant by a Least Cost Plan?
How many types of joins are there in teradata?
How do you set the session mode parameters in bteq?
There is a load to the Table every one hour and 24/7.Morning trafic is high, afternoon trafiic is less, Night trafiic is high.According to this situation which Utility you use and how do you load,which utility used?
Teradata 
