i have a table
col1
10
20
30
40
10
20
50
my requirement is how to retrive only duplicates like
10
10
20
20
like this how it's possible in SQL?

Answers were Sorted based on User's Feedback



i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / sivaprakasam

select * from table1 where id in (select id from table1
group by id having COUNT(id) > 1)

Is This Answer Correct ?    29 Yes 3 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / prathapreddy

select col1,count(*) from <table name> group by col1 having
count(*)>1;

Is This Answer Correct ?    7 Yes 1 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / sudheer

the below query is to get only duplicate records
i.e..,
10
10
20
20

select * from sno
where sno in (select sno from ex
group by sno
having count(sno)>1
)

and the above query is to get only
10
20

Is This Answer Correct ?    4 Yes 0 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / subhash

From above all, these 3 are the correct ways using SQL:
A.
select * from emp as p where rowid<(select max(rowid) from
emp s where p.deptno=s.deptno);
B.
select * from sno
where sno in (select sno from emp
group by sno
having count(sno)>1
)
C.
1. SELECT A.COL1 FROM TABLE1 A

2. ( SELECT COL1, COUNT(COL1)FROM TABLE1
GROUP BY (COL1)
HAVING COUNT(COL1)=1)B

WHERE

3. A.COL1<>B.COL1

RESULT : 1. 10 20 30 40 10 20 50
2. 30 40 50
3. 10 10 20 20
And Using DataStage:
A.
Take source and copy,aggregate,JOIN,FILTER
1) give one copy output link to aggr, other to JOIN

2)In arggr perform count rows,
col1 COUNT
10 2
20 2
30 1
40 1
50 1

3) JOIN the two links(link1 from copy, link2 from AGG) and
use LEFT OUTER JOIN
col1 COUNT
10 2
20 2
30 1
40 1
10 2
20 2
50 1

4)And in FILTER, give constrains as count=1 to TARGET1 and
count>1 to TARGET2. in the TARGET2, u will get the desired
output.

Is This Answer Correct ?    4 Yes 0 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / d.bharath

Take source and copy,aggregate,transformer and lookup,
1) give one copy output link to aggr,

2)In arggr perform count rows,

3)And in transformer check constrains count>1,count>2

4)And lookup the result of count>2 with the second output
link of copy stage.the you will get the all repeated rows
into on one output dataset.

Is This Answer Correct ?    3 Yes 0 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / ramesh

SQL:

1. SELECT A.COL1 FROM TABLE1 A

2. ( SELECT COL1, COUNT(COL1)FROM TABLE1
GROUP BY (COL1)
HAVING COUNT(COL1)=1)B

WHERE

3. A.COL1<>B.COL1

RESULT : 1. 10 20 30 40 10 20 50
2. 30 40 50
3. 10 10 20 20

Is This Answer Correct ?    2 Yes 0 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / vamsi

HI,
ANY ONE TELL ME HO WE CAN DONE THIS IN DATASTAGE?

Is This Answer Correct ?    1 Yes 1 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / mcssrinivas009

select RANK , D *from(select roumun RANK,E. *from E) D where
RANKIN 1,2,5,6;
using this query we getting from our requirement

Is This Answer Correct ?    0 Yes 0 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / sivakrishna

select * from emp as p where rowid<(select max(rowid) from
emp s where p.deptno=s.deptno);

Is This Answer Correct ?    2 Yes 2 No

i have a table col1 10 20 30 40 10 20 50 my requirement is how to retrive only duplicates l..

Answer / geetha

To achive the same in datastage we can use aggregator stage.

Check the below link
http://mydatastage-notes.blogspot.in/p/aggregatorstage.html

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More Data Stage Interview Questions

Converting Vertical PIVOTing without using PIVOT stage in DataStage. Ex: DEPT_NO EMPNAME 10 Subhash 10 Suresh 10 sravs Output: DEPT_NO EMP1 EMP2 EMP3 10 subhash suresh sravs 2) How to implement Horizontal PIVOTing without using PIVOT stage.

3 Answers   Cognizant, UHG,


What could be a data source system?

0 Answers  


1.how to generate even numbers in surrogate or tranformar stage ? 2. how many ways to remove duplicate values?

5 Answers   Gallega, TCS,


What is the command line function to import and export the ds jobs?

0 Answers  


what r the sources u need to create the datastage?

1 Answers  






What is the difference between SQl Loader and OCI in datastage?

2 Answers   CSC,


what is snow flack schema?

2 Answers  


WHAT IS FORCE COMPILE?

3 Answers   FIS, HCL, Polaris,


WAT TYPE OF PL/SQL QUERRYS USED IN DATASTAGE

0 Answers  


What is the difference between informatica and datastage?

0 Answers  


EXPLAIN SCD

0 Answers   CTS,


What is the difference between account and directory options ?

0 Answers  


Categories