write a query for how to eliminate the duplicate rows
without using distinct?

Answers were Sorted based on User's Feedback



write a query for how to eliminate the duplicate rows without using distinct?..

Answer / mani

DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID)FROM
EMP)GROUP BY EMPNO;

Is This Answer Correct ?    10 Yes 2 No

write a query for how to eliminate the duplicate rows without using distinct?..

Answer / madhu

DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID)FROM
EMP GROUP BY EMPNO);

Is This Answer Correct ?    6 Yes 0 No

write a query for how to eliminate the duplicate rows without using distinct?..

Answer / abhishek kumar

select id from tgt_table group by id;

Is This Answer Correct ?    7 Yes 4 No

write a query for how to eliminate the duplicate rows without using distinct?..

Answer / czarabid

it's very simple the question is to delete the duplicated
records and it's solution is here
DELETE FROM TABLE_NAME1 t1 WHERE ROWID </> any(SELECT ROWID
FROM TABLE_NAME1 t2 WHERE t1.column1=t2.column1);
this will delete only the redundant records leaving only one
copy of the same records in the table.
If you want to delete all the records which are redundant
then the solution is here
DELETE from table_name1 t1 where rowid <> any (select rowid
from table_name1 t2 where t1.no=t2.no)

Is This Answer Correct ?    0 Yes 0 No

write a query for how to eliminate the duplicate rows without using distinct?..

Answer / murali udayagiri

Assume some duplicate data is there in emp table. Try the
below to eliminate duplicate.

select * from emp
union
select * from emp;


Thanks,
Murali Udayagiri

Is This Answer Correct ?    2 Yes 5 No

write a query for how to eliminate the duplicate rows without using distinct?..

Answer / bharu_295

select count(*),col1,col2 ..... from table_name
groupby col1,col2.....
having count(*)>1

Is This Answer Correct ?    2 Yes 9 No

Post New Answer

More Informatica Interview Questions

Implementation methodology

0 Answers  


define galaxy repository in informatica

0 Answers  


Comment on significance of oracle 9i in informatica when compared to oracle 8 or 8i?

1 Answers  


What are the features of complex mapping?

0 Answers  


There are 2 files, Master and User. We need to compare 2 files and prepare a output log file which lists out missing Rolename for each UserName between Master and User file. Please find the sample data- MASTER.csv ---------- Org|Tmp_UsrID|ShortMark|Rolename ---|---------|----------|------------ AUS|0_ABC_PW |ABC PW |ABC Admin PW AUS|0_ABC_PW |ABC PW |MT Deny all GBR|0_EDT_SEC|CR Edit |Editor GBR|0_EDT_SEC|CR Edit |SEC MT103 GBR|0_EDT_SEC|CR Edit |AB User USER.csv -------- Org|UserName|ShortMark|Rolename ---|--------|---------|------------ AUS|charls |ABC PW |ABC Admin PW AUS|amudha |ABC PW |MT Deny all GBR|sandya |CR Edit |Editor GBR|sandya |CR Edit |SEC MT103 GBR|sandya |CR Edit |AB User GBR|sarkar |CR Edit |Editor GBR|sarkar |CR Edit |SEC MT103 Required Output file: --------------------- Org|Tmp_UsrID|UserName|Rolename |Code ---|---------|--------|------------|-------- AUS|0_ABC_PW |charls |ABC Admin PW|MATCH AUS|0_ABC_PW |charls |MT Deny all |MISSING AUS|0_ABC_PW |amudha |ABC Admin PW|MISSING AUS|0_ABC_PW |amudha |MT Deny all |MATCH GBR|0_EDT_SEC|sandya |Editor |MATCH GBR|0_EDT_SEC|sandya |SEC MT103 |MATCH GBR|0_EDT_SEC|sandya |AB User |MATCH GBR|0_EDT_SEC|sarkar |Editor |MATCH GBR|0_EDT_SEC|sarkar |SEC MT103 |MATCH GBR|0_EDT_SEC|sarkar |AB User |MISSING Both the files are mapped through Organization, Shor_mark. So, based on each Organization, Short_Mark, for each UserName from User.csv, we need to find the Matching and Missing Rolename. I am able to bring Matching records in the output. But really I don't find any concept or logic to achieve "MISSING" records which are present in Master and not in User.csv for each UserName. Please help out guys. Let me know if you need any more information. Note:- In User.csv file, there are n number of Organization, under which n number Shortmark comes which has n number of UserName.

0 Answers  


how can u connect client to ur informatica sever iff server is located at different place( not local to the client)

3 Answers   Accenture,


Source table ------------- ID NAME --- ------ 101 PANKAJ NULL KUMAR NULL MATHUR 102 JYOTI NULL SAXENA 103 SACHIN NULL TENDULKAR TARGET TABLE ------------- ID NAME --- ------- 101 PANKAJ KUMAR MATHUR 102 JYOTI SAXENA 103 SACHIN TENDULKAR How to do the above scenario in Informatica.

4 Answers   Cap Gemini,


How do you recover rows from a failed session

1 Answers  


What is the target load order?

3 Answers   HCL, Informatica,


In Flat file Input is A B C I need output like this A A A B B B C C C. How will u achieve this?

5 Answers   iNautix,


What is data movement mode in Informatica and difference between them?

0 Answers  


I am hvaing SOURCE as first line: 1000,null,null,null second line as:null,2000,null,null 3rd line as :null,null,3000,null and final line as: null,null,null,4000 ............................Now i want the OUTPUT as 1000,2000,3000,4000 For more clarification i want to elimate nulls and want in a single line. Please help me out

2 Answers   IBM,


Categories