sqlt-sqlsybasease

Order by "In clause"

I have a query wherein I use "In" clause in it. Now I wish to have the result set in same order as my In clause. For example - 

select Id,Name from mytable where id in (3,6,7,1)

Result Set : 

|Id | Name |
------------
| 3 |  ABS |
| 6 |  NVK |
| 7 |  USD |
| 1 |  KSK |

I do not want to use any temp table. Is it possible to achieve the goal in one query?

Solution

  • In T-SQL, you can do this using a big case:

    select Id, Name
from mytable
where id in (3, 6, 7, 1)
order by (case id when 3 then 1 when 6 then 2 when 7 then 3 else 4 end);

    Or with charindex():

    order by charindex(',' + cast(id as varchar(255)) + ',',
                   ',3,6,7,1,')