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
- 59603 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What are the steps to create a data model?
Syntax for case when statement?
Backup Script was blocked you are unable to archive the data now. how do you analyze it and where do you identify ?
Explain the term 'tables' related to relational database management system?
What are the components used in smp and massively parallel processing (mpp) machines?
What are the different methods ot loading a dimension table? A fact table etc?
In general, how do you optimze any sql in teradata?
List out some of the primary characteristics of teradata.
Explain parsing engine in teradata?
What are the advantages of teradata?
What are the available primary index types in teradata.
Explain the new features of teradata?
What is the difference between union and union all in teradata?
What are aggregate tables? How do you design them using teradata?
What is the opening step in basic teradata query script?
