Keep on sort order when using IN statement in WHERE clause

Question

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....

Answer 1

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