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
- 59651 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 how spool space is used.
In BTEQ, how are the session-mode parameters being set?
What is the purpose of upsert command?
What are the functions of a Parser?
How to view every column and the columns contained in indexes in teradata?
What are the scenarios in which full table scans occurs?
Name the five phases that come under MultiLoad Utility.
How would you load a very large file in teradata in general? What utility would you use for it? Why?
In Teradata, what is the significance of UPSERT command?
Backup Script was blocked then you are unable to archive the data. how do you analyze it and where do you identify ?
Explain the term 'primary key' related to relational database management system?
What are the updated features of teradata?
List out all forms of LOCKS that are available in Teradata.
Why is the case expression used in teradata?
List out teradata data types?
