[thelist] Ordering a recordset by an array

Ben Gustafson Ben_Gustafson at lionbridge.com
Tue Oct 14 14:16:38 CDT 2003


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:

function getOrderedRS(arr)
{
	var strIDs = "";
	var strOrderBy = " order by ";
	for (var i=0; i<arr.length; i++)
	{
		strIDs += arr[i] + ",";
		strOrderBy += " case when id=" + arr[i] + " then 1 else 0
end desc, ";
	}
	strIDs = strIDs.substring(0, strIDs.lastIndexOf(",")); // remove
trailing comma
	strOrderBy = strOrderBy.substring(0, strOrderBy.lastIndexOf(","));
	var sql = " select * from my_table where id in (" + strIDs + ") " +
strOrderBy;
	var rs = conn.Execute(sql);
	return rs;
}

One caveat: you can't have the same ID more than once in your Order By
clause. As Microsoft OLE DB Provider for SQL Server error '80040e14' says:
"A column has been specified more than once in the order by list. Columns in
the order by list must be unique."

Anthony, I got kind of spooked by the specter of converting your VBScript
code to JavaScript. It probably would have worked, too.

Thanks, all.

--Ben

> ben, here's a solution using standard sql --
>
>   select * from yourtable
>   where id in (17,5,10)
>   order by 
>     case when id=17 then 1 else 0 end desc
>   , case when id=5  then 1 else 0 end desc
>   , case when id=10 then 1 else 0 end desc
>
> requires no sort column 
>
> easy enough to generate on the fly, as each invocation
> of the query will likely be a different set of numbers
>
> rudy


More information about the thelist mailing list