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?
- 11 Answers
- 17961 Views
- Cap Gemini, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
Answer / prathapreddy
select col1,count(*) from <table name> group by col1 having
count(*)>1;
| Is This Answer Correct ? | 7 Yes | 1 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
detail about sdlc
I want capture UnMatched records from Primary source and secondary source in JOIN stage?
Hi friends If anybody completed datastage 8.5 certification.Could please guide me what are the topics new in 8.5 certification.
How will you load you daily/monthly jobs datas in to Fact and Dimension table using datastage.
explain about completely flow of sequencers technicaly,without using example??explain about lookup,nullhandling?
how can we do null handling in sequential files
How many Nodes configuration file you used in your last project?
how to load meta data
tell me abt Datastage trigger?
How do you start developing a datastage project?
What is ibm datastage?
Explain usage analysis in datastage?
Teradata 
