Skip to content
Advertisement

How to SORT in order as entered in SQL Server?

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement