[thelist] Ordering a recordset by an array

rudy rudy937 at rogers.com
Tue Oct 14 13:16:09 CDT 2003


apologies if this gets posted twice; webmail glitches


> Select *, 
>        decode(id, 15, 1, 7, 2, 10, 3, 4) as sort_order
> From my_table
> Where id in (15, 7, 10)
> Order by sort_order

hi oren

that'll only work in oracle

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