How to join two tables together with same number of rows by their order

I am using SQL2000 and I would like to join two table together based on their positions

For example consider the following 2 tables:

table1
-------
name
-------
'cat'
'dog'
'mouse'

table2
------
cost
------
23
13
25

I would now like to blindly join the two table together as follows based on their order not on a matching columns (I can also guarantee both tables have the same number of rows):

-------|-----
name   |cost
-------|------
'cat'  |23
'dog'  |13
'mouse'|25

Is this possible in a T-SQL select??

Edit Report

1 Answer

Xynth - built in row numbering is not available until SQL2K5 unfortunately, and the example given by microsoft actually uses triangular joins - a horrific hidden performance hit if the tables get large. My preferred approach would be an insert into a pair of temp tables using the identity function and then join on these, which is basically the same answer already given. I think the two-cursors approach sounds much heavier than it needs to be for this task.

answered 2009-04-27T12:09:44.340

How to join two tables together with same number of rows by their order

1 Answer

Your Answer