How to eliminate duplicate rows in oracle sql

I know this is stupid question to ask but just giving a try if any one can help me.


I have two tables A & B

Table A Columns
-------------------

ID           AMOUNT
1               100
1               200
1               300
1               400
1               500

Table B columns
--------------------

ID              NO
1               ABC
1               DEF
1               GHI
1               JKL
1               MNO


When I join both the tables I get 5*5 = 25 rows

Is there any way I can get 5 records when I join both the tables with ID as Join ?







VenkatBunnyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Add a where clause?

Select t1.id, amount, no
From tableA t1, tableB t2
Where t1.id=t2.I'd;
0
 
VenkatBunnyAuthor Commented:
I join with ID I still get 25 rows
0
 
slightwv (䄆 Netminder) Commented:
OH, I see it now.

There really aren't duplicates.

How do you relate what amount goes with what NO?

Does 100 go with ABC or MNO?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Gerwin Jansen, EE MVETopic Advisor Commented:
How should your 5 row output look like?
0
 
slightwv (䄆 Netminder) Commented:
You can create an arbitrary join column but there you need to decide on what amount goes with what NO.

The code below produces:


        ID     AMOUNT N
---------- ---------- -
         1        100 a
         1        200 b
         1        300 c
         1        400 d
         1        500 e
drop table tab1 purge;
create table tab1(id number, amount number);

drop table tab2 purge;
create table tab2(id number, no char(1));

insert into tab1 values(1,100);
insert into tab1 values(1,200);
insert into tab1 values(1,300);
insert into tab1 values(1,400);
insert into tab1 values(1,500);

insert into tab2 values(1,'a');
insert into tab2 values(1,'b');
insert into tab2 values(1,'c');
insert into tab2 values(1,'d');
insert into tab2 values(1,'e');
commit;

select t1.id, t1.amount, t2.no
from
(select id, amount, row_number() over(order by amount) t1_rownum from tab1) t1,
(select id, no, row_number() over(order by no) t2_rownum from tab2) t2
where t1.id=t2.id and t1.t1_rownum = t2.t2_rownum;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Piloute,

Isn't that basically what I posted?
0
 
PilouteCommented:
Humm,

Yup ! Looks similar.

What I want to point out here is that VenkatBunny probably didn't search for the problem before posting a question...

Cheers,
P
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.