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?
- 11 Answers
- 20950 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 |
how to use self join using datastage ? can u tell me using stage how can we implemnet the self join
diff between changecapture & diffrence stage which situvation we can use the diffrence stage ?
SOURCE CITY NAME1 NAME2 NAME3 NAME4 BGLR HCL CSC IBM TCS TARGET CITY COMPANY BGLR HCL BGLR CSC BGLR IBM BGLR TCS
I have a scenario like Deptno=10---->First record and last record Deptno=20---->First record and last record Deptno=30---->First record and last record I want those first and last records from each department in a single target. How to do this in DataStage, any one can assist me. Thanks in advance.
Can you highlight the main features of ibm infosphere information server?
What is the sortmerge collector?
If you want to use the same piece of code in different jobs, how will you achieve it?
What is the Difference between Change capture stage and Difference Stage ? What are its significance individually ?
If you want to use a same piece of code in different jobs, how will you achieve this?
What is a merge?
What are data elements?
in sequtial file 2 columns avaliable,iwant only one column load the target how do it.
Teradata 
