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
- 59584 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
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 normalization, first normal form, second normal form and third normal form?
What is primary index and secondary index?
What is meant by a Clique?
What are the string manipulation operators and functions associated with teradata?
Write a program to show the parser component used in teradata?
What is smp and mpp platforms?
Which is more efficient group by or distinct to find duplicates?
What are the multiload utility limitations?
What is the difference between fastload and multiload?
Explain the teradata primary index mechanics in detail with a diagram.
What is the use of having index on table?
Difference between stored procedure and macro?
what is sysdba and sysdbc ? which has high priority ?
What is spool space? Why do you get spool space errors? How do trouble-shoot them?
