[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