I have two simple one column tables like this:
Table one:
from_date
01.01.18
15.01.18
23.01.18
01.02.18
Table two:
to_date
15.01.18
23.01.18
01.02.18
05.02.18
All I want to do is join them on a.row_number = b.row_number
I can't find a way within Ingres SQL to do this. The best idea I have is to add a row number column to each table and join on this. I have come across the CREATE SEQUENCE function, but I can't find clear instructions on how to apply it
According to the manual Ingres supports window functions and row_number(), so you should be able to do:
select coalese(a.rn, b.rn) as rn, a.from_date, b.to_date
from (
select from_date, row_number() over (order by from_date) as rn
from table_a
) a
full join (
select to_date, row_number() over (order by to_date) as rn
from table_b
) b on a.rn = b.rn;
The full (outer) join ensures that rows from both tables are returned even if one tables has fewer rows than the other. If you are 100% sure that both tables always contain the same number of rows, you can use an inner join instead (remove the full keyword)
I don't have Ingres to test this, so although the above is ANSI standard SQL, there might be things that Ingres does not support
Simpler without full outer join and coalesce():
select a.rn, a.from_date, b.to_date
from (
select from_date, row_number() over (order by from_date) as rn
from table_a
) a
join (
select to_date, row_number() over (order by to_date) as rn
from table_b
) b on a.rn = b.rn;
with temp1 as (
select row_number() over (order by from_date) as row from tableA
),
temp2 as (
select row_number() over (order by from_date) as row from tableB
)
select a.,b.
from temp1 a
join temp2 b on a.row=b.row