[thelist] Ordering a recordset by an array

[Joshua Olson]

----- Original Message ----- 
From: "Ben Gustafson" <Ben_Gustafson at lionbridge.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, October 14, 2003 3:16 PM
Subject: Re: [thelist] Ordering a recordset by an array


> Thing o' beauty, Rudy. For the edification of others, here's the function
I
> created, based on Rudy's SQL, for returning a recordset ordered in the
order
> of the IDs in the array passed in as an argument:

Rudy, et al,

Here's an sp to do about the same thing.  It takes the list, populates a
temp table, the joins against it to get the ordering.  It doesn't choke on
duplicates... you just get the record twice:

CREATE PROCEDURE select_list_in_order
@thelist varchar(1000) = NULL
AS
 DECLARE @pos int
 DECLARE @value varchar(20)

 CREATE TABLE #temp ( id int null, rank int identity)

 Set @thelist = @thelist  + ','
 WHILE PatIndex('%,%' , @thelist) <> 0
  BEGIN
   SET @pos = PatIndex('%,%' , @thelist)
   SET @value = Left(@thelist, @pos - 1)
   SET @thelist = Stuff(@thelist, 1, @pos, '')
   INSERT INTO #temp (id) VALUES (Convert(int, @value))
  END
 SELECT *
 FROM thetable
 INNER JOIN #temp
 ON #temp.id = thetable.id
 ORDER BY #temp.rank

 DROP TABLE #temp
GO

Usage: EXEC select_list_in_order '1,5,10,12,5,8,2,1'

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168