CODEWITHSUNDEEP
sqlsql-server-2005where-in
Liran Ben Yehuda
Liran Ben Yehuda

Reputation: 1588

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

Upvotes: 2

Views: 1137

Answers (2)

iandayman
iandayman

Reputation: 4467

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453707

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

Upvotes: 3

Related Questions