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  |
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   |
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  |
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?
|
|
|
Goto Forum:
Current Time: Tue Feb 11 20:23:06 CST 2025
|