Home » SQL & PL/SQL » SQL & PL/SQL » How can I do a one-to-one join without keys
How can I do a one-to-one join without keys [message #194095] Wed, 20 September 2006 15:26 Go to next message
dehnni
Messages: 1
Registered: September 2006
Junior Member
Suppose I have two tables A (A1, A2) and B (B1, B2)
that contain the same number of rows.
I want a query that gives me C (A1, A2, B1, B2)
where each pair of rows of equals rank with respect to A1 resp. B1, form a new row.

A (3,4) (5,6) (1,9) sort (1,9) (3,4) (5,6)
B (9,0) (2,2) (4,3) ---> (2,2) (4,3) (9,0)

"merge"
------> C (1,9,2,2) (3,4,4,3) (5,6,9,0)

Is there an easy way ?

regards
Nils
Re: How can I do a one-to-one join without keys [message #194117 is a reply to message #194095] Wed, 20 September 2006 17:23 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Weird task. Must b ea very strange datamodel!

How about this:

with ax as (select rownum, a1, a2 from a order by a1, a2)
, bx as (select rownum, b1, b2 from b order by b1, b2)
select a1, a2, b1, b2
from ax, bx
where ax.rownum = bx.rownum


Br
Kim Anthonisen
Re: How can I do a one-to-one join without keys [message #194177 is a reply to message #194117] Thu, 21 September 2006 02:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not a datamodel, thats a disaster in progress.

I thought I'd come up with another way of doing it, but it's just the same as @kimant's, only using analytic row_number instead of rownum.

What happens when you've got two values of A1 the same?
Previous Topic: IN Clause Problem
Next Topic: Regarding query
Goto Forum:
  


Current Time: Fri Dec 09 04:17:37 CST 2016

Total time taken to generate the page: 0.11676 seconds