Golgappa.net | Golgappa.org | BagIndia.net | BodyIndia.Com | CabIndia.net | CarsBikes.net | CarsBikes.org | CashIndia.net | ConsumerIndia.net | CookingIndia.net | DataIndia.net | DealIndia.net | EmailIndia.net | FirstTablet.com | FirstTourist.com | ForsaleIndia.net | IndiaBody.Com | IndiaCab.net | IndiaCash.net | IndiaModel.net | KidForum.net | OfficeIndia.net | PaysIndia.com | RestaurantIndia.net | RestaurantsIndia.net | SaleForum.net | SellForum.net | SoldIndia.com | StarIndia.net | TomatoCab.com | TomatoCabs.com | TownIndia.com
Interested to Buy Any Domain ? << Click Here >> for more details...


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

how to validate the jobs

2 Answers   IBM,


How do you import and export data into datastage?

0 Answers  


Create a job to load all unique products in one table and the duplicate rows in to another table. The first table should contain the following output A D The second target should contain the following output B B B C C Q2. Create a job to load each product once into one table and the remaining products which are duplicated into another table. The first table should contain the following output A B C D The second table should contain the following output B B C

3 Answers   Deloitte, IBM,


Define Job control?

0 Answers  


Differentiate between validated and Compiled in the Datastage?

0 Answers  


Explain connectivity between datastage with datasources?

0 Answers  


how can we send even and odd records from a sequential file to two different targets

10 Answers   IBM, iGate, TCS,


How many types of stage?

0 Answers  


I want capture UnMatched records from Primary source and secondary source in JOIN stage?

3 Answers   TCS,


What is container and then types?

1 Answers  


Describe the architecture of datastage?

0 Answers  


What are the third party tools you used in your project?

1 Answers   IBM,


Categories