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
- 21578 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 delete one row in target dataset
What are the different plug-ins stages used in your projects?
what is the difference between == and eq in UNIX shell scripting?
In Datastage, how you can fix the truncated data error?
Define Job control?
what is the main difference between sorragate key n primary key in one word
3) Sequential file contains data like Empno ename sal 111 abc 2000 Trgt file: Trgt1----111 Trgt2----abc Trgt3---2000
EXPLAIN SCD
how to define satge variables in transformer stage
What is confirmed Dimension? what is Factless Fact? give one example? What are Additive, Semi-Additive Facts?
if a column contains data like ram,rakesh,madhan,suraj,pradeep,bhaskar then I want to place names separated by commas in another columns how can we do?
hi this is kiran i have one table i want divide the table with two different table like even rows and odd rows how can i do this one tell me plzz
Teradata 
