I’m using SQL Server and I’m trying to find results but I would like to get the results in the same order as I had input the conditions.
My code:
SELECT AccountNumber, EndDate FROM Accounts WHERE AccountNumber IN (212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689) -- I would like the results to be in the same order as these numbers.
Advertisement
Answer
Here is an in-line approach
Example
Declare @List varchar(max)='212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689' Select A.AccountNumber ,A.EndDate From Accounts A Join ( Select RetSeq = Row_Number() over (Order By (Select null)) ,RetVal = v.value('(./text())[1]', 'int') From (values (convert(xml,'<x>' + replace(@List,',','</x><x>')+'</x>'))) x(n) Cross Apply n.nodes('x') node(v) ) B on A.AccountNumber = B.RetVal Order By B.RetSeq
EDIT – the subquery Returns
RetSeq RetVal 1 212345 2 312345 3 145687 4 658975 5 256987 6 365874 7 568974 8 124578 9 125689