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
- 59976 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
Explain fallback in teradata?
How to explain project Architecture and flow in teradata interviews?Can please anyone help on this? Am new to teradata.
What does Amp contain and what are all the operations that it performs?
What interface is used to connect to windows based applications?
What are the various etl tools in the market?
How to identify ppi columns?
In Teradata, what is the significance of UPSERT command?
What are the steps involved after the disk manager passes the request?
What is meant by a Least Cost Plan?
Highlight the advantages of PPI(Partition Primary Index).
what are the uses of fact table and dimension table in banking project?
What is meant by a node?
What is a three-tier data warehouse?
What is the syntax for case when statement?
Can any one explain me the difference between BTEQ and MLOAD,TUMP. All canbe used for same purpose but still differnt methods. why ?
