Ordering SQL Server results by IN clause

Question

I have a stored procedure which uses the IN clause. In my ASP.NET application, I have a multiline textbox that supplies values to the stored procedure. I want to be able to order by the values as they were entered in the textbox. I found out how to do this easily in mySQL (using FIELD function), but not a SQL Server equivalent.

So my query looks like:

Select * from myTable where item in @item

So I would be passing in values from my application like '113113','112112','114114' (in an arbitrary order). I want to order the results by that list.

Would a CASE statement be feasible? I wouldn't know how many items are coming in the textbox data.

score 5 · Accepted Answer · answered Jan 20 '12 at 20:21

5

How are you parameterising the IN clause?

As you are on SQL Server 2008 I would pass in a Table Valued Parameter with two columns item and sort_order and join on that instead. Then you can just add an ORDER BY sort_order onto the end.

answered Jan 20 '12 at 20:21

Martin Smith

438,706
87
741
845

2

+1, or if by csv, split into a @table variable with an identity column which you can join to and then order by – KM. Jan 20 '12 at 20:24
@KM so OP needs to write a store procedure for a simple select – Saic Siquot Jan 20 '12 at 20:45
@LuisSiquot - They are already using a stored procedure. But they should be using parameterised queries even if they weren't. There are plenty of split table valued functions around that operate as suggested by KM that the OP could join onto but if 2005 compatibility isn't needed TVPs will be better. – Martin Smith Jan 20 '12 at 20:49
select Ordering by the order of values in a SQL IN() clause check [this](http://stackoverflow.com/a/36722628/2218697) **solution** . hope helps someone. – Shaiju T Apr 19 '16 at 15:19

Saic Siquot · Answer 2 · 2015-04-04T13:25:46.980

2

the same way you concatenate ('113113','112112','114114') to pass to the sql sentence in the where clausule you can concatenate

order by
case item    
when '113113' then 1
when '112112' then 2
when '114114' then 3
end

to pass to your order by clausule

edited Apr 04 '15 at 13:25

answered Jan 20 '12 at 20:29

Saic Siquot

6,513
5
34
56

Not possible he doesn't know how many values there will be - your case statement could be from 1 to n where n is really really really high. – JonH Jan 20 '12 at 20:30
@JonH perhaps he knows how many values there will be. Of course this solution is for a finite and "logical" n cases. OP can evaluate if a little n fits his needs. Be sure that if this is for a presentation issue n covers all logical cases. – Saic Siquot Jan 20 '12 at 20:42
I agree with you however he mentioned he does not know how many. Hence my comment. – JonH Jan 20 '12 at 20:47
@JonH but the values "are coming in the textbox data" – Saic Siquot Jan 20 '12 at 20:50
Coming in the textbox data, means it could be dynamic - someone could be entering 113, 114, 115, 116 and so on, and your case statement would have to handle all those. My point is not that you are wrong - my point is your solution isn't flexible enough. It has to know how many WHEN statements for the CASE. – JonH Jan 20 '12 at 20:54
@JonH if someone is entering (or selecting) the n can't be as big to say that this solution is not flexible, because for any amount of choices shown to an human that he can type (or select) this solution works fine. An sql statment can handle typycaly up to 268.435.456 chars, textbox and a human can handle the same amount? cut&paste? – Saic Siquot Jan 20 '12 at 21:17
@Luis Siquot - JonH is correct. People can enter 113,144,123,etc and it is a variable that I'm not prepared to code a dynamic case statement to handle all N occurrences - the permutations would be too great. I'm looking for the easiest way, which I think Martin has offered. – Andrew Jan 20 '12 at 22:05
This would be brutal to implement, and likely not very performant. – Haldrich98 Feb 23 '15 at 20:43
1

Whether this answer is sufficiently responsive to the particular question is debatable, it seems, but I found it useful for a similar problem. The deprecation (and debate) seems a bit excessive. – fortboise Jun 19 '15 at 22:44

JonH · Answer 3 · 2012-01-20T20:59:56.617

From KM's comment above...

I know you didn't state it is comma seperated, but if it was a CSV or even if you have it space seperated you could do the following.

DECLARE @SomeTest varchar(100) --used to hold your values
SET @SomeTest = (SELECT '68,72,103') --just some test data

SELECT 
    LoginID --change to your column names
FROM 
    Login   --change to your source table name
INNER JOIN
(   SELECT 
    * 
    FROM fn_IntegerInList(@SomeTest)
) n
ON 
    n.InListID = Login.LoginID
ORDER BY 
    n.SortOrder

And then create fn_IntegerInList():

CREATE FUNCTION [dbo].[fn_IntegerInList] (@InListString ntext)
RETURNS @tblINList TABLE (InListID int, SortOrder int)
AS
BEGIN
declare @length int
declare @startpos int
declare @ctr int
declare @val nvarchar(50)
declare @subs nvarchar(50)
declare @sort int

set @sort=1
set @startpos = 1
set @ctr = 1
select @length = datalength(@InListString)

while (@ctr <= @length)
begin
   select  @val = substring(@InListString,@ctr,1)

   if @val = N',' 
     begin
        select @subs = substring(@InListString,@startpos,@ctr-@startpos)
        insert into @tblINList values (@subs, @sort)
        set @startpos = @ctr+1
     end
  if @ctr = @length 
      begin
        select @subs = substring(@InListString,@startpos,@ctr-@startpos)
        insert into @tblINList values (@subs, @sort)
      end
  set @ctr = @ctr +1
  set @sort = @sort + 1
end
RETURN
END

This way your function creates a table that holds a sort order namely, SortOrder and the ID or number you are passing in. You can of course modify this so that you are looking for space rather then , values. Otherwise Martin has the right idea in his answer. Please note in my example I am using one of my tables, so you will need to change the name Login to whatever you are dealing with.

Ordering SQL Server results by IN clause

3 Answers3

Linked

Related