Keep on sort order when using IN statement in WHERE clause

I'm using SQL Server 2005.

I have a temporary sorted table (Table_A) that contains 2 columns (ID, RowNumber).

Now, I create a new table by selecting all rows from other table (Table_B) that exist (ID value) in the temporary table (Table_A).

SELECT * 
FROM Table_B
WHERE Table_B.ID IN (SELECT ID FROM Table_A)

The results of the query above is not sorted by Table_A sorting.

I'm looking for a way to keep 开发者_如何学编程the results of the new result table sorted by the Table_A sorting.

Tx....

You'll need to use a JOIN instead. I have assumed below that Table_A can only have 1 row per ID. If this is not the case then rewriting as a JOIN will introduce duplicate rows and we will need more details of which RowNumber to use for sorting purposes in that case.

SELECT Table_B.* 
FROM Table_B JOIN Table_A ON  Table_B.ID = Table_A.ID
ORDER BY Table_A.RowNumber

select b.* from Table_B b join Table_A a on a.id = b.id order by a.RowNumber