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

Tables aren't sorted - only result sets (and cursors) are. – Damien_The_Unbeliever May 23 '11 at 09:40 — Damien_The_Unbeliever, May 23 '11 at 09:40

score 3 · Accepted Answer · answered May 23 '11 at 09:23

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

score 0 · Answer 2 · answered May 23 '11 at 09:24

0

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

answered May 23 '11 at 09:24

iandayman

4,357
31
38

Keep on sort order when using IN statement in WHERE clause

2 Answers2