-1

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?

KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32
Niraj Kulkarni
  • 35
  • 1
  • 5
  • Put 3,6,7,1 in a table, INNER JOIN to it, ORDER BY it – Alex K. Oct 06 '14 at 14:02
  • Is this interview questions? Why would you want to do it at all? And why using just 1 query? If it's a genuine requirement, you may have thousands of rows instead of just 4. – Meet Oct 07 '14 at 09:42
  • @Meet It is not interview question but yes a colleague from work tricked me with this, and he does take interviews for our team :-) – Niraj Kulkarni Oct 30 '14 at 13:24

2 Answers2

3

You can use CTE as well 

with filterID as 
(
  3 ID, 1 as sequence
  union 
  6, 2
  union 
  7, 3
  union 
  1, 4
)

select mytable.* from mytable 
inner join filterID on filterID.ID = mytable.ID
order by filterID.sequence ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
NMK
  • 1,010
  • 10
  • 18
2

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,')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786