CODEWITHSUNDEEP
sqlsql-servert-sql
Chase
Chase

Reputation: 584

Return SQL query results in order of input into WHERE clause

Using: SSMS 2008, t-SQL

Goal: Return results of query in the same order that I input them into the WHERE clause

Example:

select *
from LoanTable
where LoanNumber in (3,2,4,5,1)

Yes, the simplified query here runs, but the results are returned in the order 1,2,3,4,5. When instead I would like them to be returned in the order 3,2,4,5,1 (the order of input into the WHERE clause).

Additional Information: The reason for this is because I use a VBA loop in Excel to generate coversheets for each loan number typed into a column in an Excel workbook, and the cover sheets need to be generated/printed off in the order that the operator inputs them. This allows the operator to marry them with another corresponding document easily. But if the loan numbers come back from the SQL query in a different order, this marrying process becomes quite difficult when the operator is dealing with hundreds of coversheets generated.

Upvotes: 7

Views: 12324

Answers (5)

jl_
jl_

Reputation: 5539

I would go by creating a Table type to hold the LoanNumber's to be selected. Doing so, the query would look more readable. 

DECLARE @LoanNumberTable TABLE
(
  [ID] INT IDENTITY (1,1), 
  [LoanNumber] INT    
);

INSERT INTO @LoanNumberTable VALUES (3), (2), (4), (5), (1);

SELECT LoanTable.* 
FROM LoanTable 
INNER JOIN @LoanNumberTable AS Temp ON LoanTable.LoanNumber = Temp.LoanNumber
ORDER BY Temp.ID;

Upvotes: 2

SlimsGhost
SlimsGhost

Reputation: 2909

Since the order by clause is the only way to ensure result set order, you would have to build an order by clause to match your where clause, like this:

select *
from LoanTable
where LoanNumber in (3,2,4,5,1)
order by case LoanNumber
when 3 then 1
when 2 then 2
when 4 then 3
when 5 then 4
when 1 then 5
else 9999 end

If you're building the where dynamically, this wouldn't be too bad though, as the logic for order by would match at every step.

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294487

The construct (3,2,4,5,1) is a set. It contains no order and one cannot ask for an ORDER BY based on it. What you need is to pass in a table valued parameter with two fields, one for LoanNumber and one for desired order (rank). The query by joining the TVP:

SELECT ...
FROM LoanTable
JOIN @tvp t ON LoanTable.LoanNumber = t.LoanNumber
ORDER BY t.Order;

Read more about table valued parameters.

Upvotes: 2

paparazzo
paparazzo

Reputation: 45106

select *
from LoanTable
join (values (3,1), (2,2), (4,3), (5,4), (1,5)) v(Id, Or)
on LoanTable.LoanNumber  = v.ID 
order by v.Or

Upvotes: 0

swe
swe

Reputation: 1455

IF you know your order-expression is numeric, you could do somthing like that:

select *, abs(sign(LoanNumber-3)) as Ord1, abs(sign(LoanNumber-2)) as Ord2, abs(sign(LoanNumber-4)) as Ord3...
from LoanTable
where LoanNumber in (3,2,4,5,1)
ORDER BY Ord1, Ord2, Ord3

By that, you build a difference per Input, get 1 or 0 if it meets your ordercriteria, and order by that.

This kind of thing is maybe only to be used with a small resultset...

Upvotes: -1

Related Questions