There is no such thing as the "original row number" in a relational database.
A row number can only be defined when you use an order by.
So assuming you
have a column to sort both tables you can do the following:
Code:
with first_ordered (
select *,
row_number() over (order by some_column) as rn
from first_table
),
second_ordered (
select *
row_number() over (order by some_column) as rn
from second_table
)
select fo.*, so.*
from first_ordered fo
join second_ordered so on fo.rn = so.rn
The CTE (with ...) is not really necessary, you can join on derived tables as well, if you prefer that syntax:
Code:
select fo.*, so.*
from (select *,
row_number() over (order by some_column) as rn
from first_table
) fo
join (select *,
row_number() over (order by some_column) as rn
from second_table) so on fo.rn = so.rn
But why you want to do that I cannot imagine...
Maybe if you told us what you
really want to do, we could find a better (= less unpredictable) solution .
dBforums
>
join of two tables on the basis of their row numbers
03-31-12, 02:38
Linear Mode
