How to do an inner join on row number in sql server

Question

SQL Server 2008

Two tables:

Table A has following data:

RowA RowB RowC RowD 

Table B has following data:

Row4 Row3 Row2 Row1 

I want to get the following output:

RowA Row1 RowB Row2 RowC Row3 RowD Row4 

The only common value between the two tables is the row number

I can get the data individually of course:

SELECT val FROM A ORDER BY val  SELECT val FROM B ORDER BY val 

But how do I join on the row number?

And what if I don't have an order-by, but just want the rows in the order they come out?

RowA Row4 RowB Row3 RowC Row2 RowD Row1 

as in the join of

SELECT val FROM A  SELECT val FROM B 

Answer 1

try this:

USE ROW_NUMBER() function in sql server 2008

select A.val,B.val  from(     SELECT val,row_number() over (order by val) as row_num     FROM A)A join     (SELECT val,row_number() over (order by val) as row_num     FROM B)B on  A.row_num=B.row_num ORDER BY A.val,B.val 

SQL fiddle demo