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
- 55600 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
What is called partitioned primary index (ppi) and discuss the advantages of using it in a query?
What do you mean by fastexport in teradata?
Hi, If anyone has TD 14 Basics dumps or study materials, please share. nirmaaal1991@gmail.com
Explain Teradata performance tuning and optimization?
Where is teradata rdbms used?
How to load specific no.of records using bteq, or fastload,or multiload
Comment whether bottleneck is an error or not.
Mention a few of the ETL tools that come under Teradata.
How to view every column and the columns contained in indexes in teradata?
If the PMON is not working then how do you manage and monitor all processes, resources and sessions etc.
Briefly explain each of the following terms related to relational database management system (rdbms) – database, tables, columns, row, primary key and foreign key.
What are the enhanced features in teradata v2r5 and v2r6?
Explain the new features of teradata?
Highlight the advantages of PPI(Partition Primary Index).
Is it necessary to add? Quit statement after a bteq query when I am calling it in a unix environment?
