How Can I Join SQL Server 2008 Tables Based on Row Numbers?

Joining Tables on Row Numbers in SQL Server 2008

To perform an inner join on row numbers in SQL Server 2008, the ROW_NUMBER() function comes into play. Let's consider the example provided:

Tables:

• Table A: Contains rows: RowA, RowB, RowC, RowD
• Table B: Contains rows: Row4, Row3, Row2, Row1

Desired Output:

• RowA Row1
• RowB Row2
• RowC Row3
• RowD Row4

Solution with an Order By Clause:

To join the tables based on row numbers, use the ROW_NUMBER() function with an ORDER BY clause:

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

Output:

This query will produce the desired output:

• RowA Row1
• RowB Row2
• RowC Row3
• RowD Row4

Solution without an Order By Clause:

If you don't have an order by clause, assigning the row numbers with the ROW_NUMBER() function will still sort the values in an arbitrary order. The rows in the output will come out in the same order as they are in the original tables:

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

Output:

Once again, this query will produce the desired output, but the order of the rows might differ:

• RowA Row4
• RowB Row3
• RowC Row2
• RowD Row1

The above is the detailed content of How Can I Join SQL Server 2008 Tables Based on Row Numbers?. For more information, please follow other related articles on the PHP Chinese website!