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
- 21307 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 |
I have 5 different sources i want same records in 5 different targets Can you any body send me this question answer rathdsetl@gmail.com
i have a small question for datastage, After the desinging (i.e., transformations and loading)part, what we can do?
what is combinability and non combinability?
I have a file it contain 2 records like empname,company as Ram, Tcs and Ram, IBM. But i want empname, company1,company2 as Ram, TCS,IBM in the target. How?
Hi I am Vijay In my source i've 10 records in a single column.... but i want to split those records into 5 sequential files each seq file contains 2 records.?.... can any body help me?
15 Answers Scope International,
How do you import and export the datastage jobs?
I have 2 Files like fileA fileB Output1 Output2 Output3 1 6 1 6 11 2 7 2 7 12 3 8 3 8 13 4 9 4 9 14 5 10 5 10 15 6 11 7 12 8 13 9 14 10 15 please let know
How many partitions does a node can have? Some body explain me clearly about the partitions and the nodes?
Hi Vijay here For Four CPU's how many nodes will required?
Tell me Wt main advantage of Stage varibles? Project level hints?
Drop duplicate records ... SOURCE LIKE .......... ID flag1 flag2 100 N Y 100 N N 100 Y N 101 Y Y 101 N Y 102 Y N 103 N N 104 Y Y 105 N N 106 N Y 102 N Y 105 Y Y in above file if any id having both the flags as "N" then that corresponding id records should be dropped, in above case o/p should be as ID flag1 flag2 101 Y Y 101 N Y 102 Y N 102 N Y 104 Y Y 106 N Y Steps to do : 1) Identified the id’s that got duplicated (both the flag values having vales “N”) 2) Look up with these id’s to existing id’s to drop .
Explain usage analysis in datastage?
Teradata 
