CODEWITHSUNDEEP
sqlsql-servert-sql
Jacob Deskin
Jacob Deskin

Reputation: 113

SQL Server: ORDER BY parameters in IN statement

I have a SQL statement that is the following:

SELECT A.ID, A.Name 
FROM Properties A 
WHERE A.ID IN (110, 105, 104, 106)

When I run this SQL statement, the output is ordered according to the IN list by ID automatically and returns

   104 West
   105 East
   106 North
   110 South

I want to know if it is possible to order by the order the parameters are listed within the IN clause. so it would return 

 110 South
 105 East
 104 West
 106 North

Upvotes: 2

Views: 1383

Answers (6)

zajonc
zajonc

Reputation: 1969

Another solution for this problem is prepare a temporary table for IN clause like

declare @InTable table (ID int, SortOrder int not null identity(1,1));

We can fill this temp table with your data in order you want

insert into @InTable values (110), (105), (104), (106);

At last we need to modify your question to use this temp table like this

select A.ID, A.Name 
from Properties A 
inner join @InTable as Sort on A.ID = Sort.ID
order by Sort.SortOrder

On the output you can see this

ID  Name
110 South
105 East
104 West
106 North

In this solution you don't need to provide order in special way. You just need to insert values in order you want.

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

What you could potentially do is:

  1. Create a TVF that would split string and keep original order.

This questions seems to have this function already written: MS SQL: Select from a split string and retain the original order (keep in mind that there might be other approaches, not only those, covered in this question, I just gave it as an example to understand what function should do)

So now if you'd run this query:

SELECT *
FROM dbo.Split('110,105,104,106', ',') AS T;

It would bring back this table as a result.

items rownum
------------
110   1
105   2
104   3
106   4

Following that, you could simply query your table, join with this TVF passing your IDs as a parameter:

SELECT P.ID, P.Name
FROM Properties AS P
INNER JOIN dbo.Split('110,105,104,106', ',') AS T
    ON T.items = P.ID
ORDER BY T.rownum;

This should retain order of parameters.

If you need better performance, I'd advice to put records from TVF into hash table, index it and then join with actual table. See query below:

SELECT T.items AS ID, T.rownum AS SortOrder
INTO #Temporary
FROM dbo.Split('110,105,104,106', ',') AS T;

CREATE CLUSTERED INDEX idx_Temporary_ID
    ON #Temporary(ID);

SELECT P.ID, P.Name
FROM Properties AS P
INNER JOIN #Temporary AS T
    ON T.ID = P.ID
ORDER BY T.SortOrder;

This should work better on larger data sets and equally well on small ones.

Upvotes: 3

Joe C
Joe C

Reputation: 3993

Here is a solution that does not rely on hard codes values or dynamic sql (to eliminate hard coding values).

I would build a table (maybe temp or variable) with OrderByValue and OrderBySort and insert from the application.

OrderByValue OrderBySort
110            1
105            2
104            3
106            4

Then I would join on the value and sort by the sort. The join will be the same as the In clause. 

SELECT A.ID, A.Name 
   FROM Properties A 
   JOIN TempTable B On A.ID = B.OrderByValue
   Order By B.OrderBySort

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82000

With the help of a parsing function which returns the sequence as well

SELECT B.Key_PS
     , A.ID
     , A.Name 
FROM Properties A 
Join (Select * from [dbo].[udf-Str-Parse]('110,105,104,106',',')) B on A.ID=B.Key_Value
WHERE A.ID IN (110,105,104,106)
Order by Key_PS

The UDF if you need

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

The Parser alone would return

Select * from [dbo].[udf-Str-Parse]('110,105,104,106',',')

Key_PS  Key_Value
1       110
2       105
3       104
4       106

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think the easiest way in SQL Server is to use a JOIN with VALUES:

SELECT p.ID, p.Name
FROM Properties p JOIN
     (VALUES (110, 1), (105, 2), (104, 3), (106, 4)) ids(id, ordering)
     ON p.id = a.id
ORDER BY ids.ordering;

Upvotes: 10

Charles Bretana
Charles Bretana

Reputation: 146557

Sure...

just add an Order clause with a case in it

 SELECT A.ID, A.Name 
 FROM Properties A 
 WHERE A.ID IN (110,105,104,106)
 Order By case A.ID 
   when 110 then 0
   when 105 then 1
   when 104 then 2
   when 106 then 3 end

Upvotes: 5

Related Questions