2

This question is derived from another question

Please refer to it for the context.

Basically I would like to use below SQL query to retrieve a list of records:

Select * From [TableA] Where [A_Design] In ("A", "D", "C" , "B")

As far as I know, "In" clause will not guarantee the retrieved record will follow the same order as ("A", "D", "C" , "B").

My question is how can we achieve this target? Or is it possible if the list is a dynamically generated long list?

Thanks to Gordon Linoff, problem solved! Below is the working solution modified from his example:

select instr(",A,D,C,B,", "," & [A_Design] & ",")
from [TableA]
where [A_Design] In ("A", "D", "C" , "B")
order by 1;

Many thanks to Luis Siquot also, your solution also works, but comparing with Instr() method, codes are little bit longer.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Wayne
  • 55
  • 1
  • 12
  • 1
    You can use `...in ('a') union .... in ('d') union ...` – artm Jan 11 '16 at 00:20
  • Hi artm, thanks for your quick response, can you elaborate more on your answer, it would be better if you can give some example codes. – Wayne Jan 11 '16 at 00:25
  • 1
    @artm Query results have no guaranteed order except as imposed by an ORDER BY. – philipxy Jan 11 '16 at 09:13
  • See also [this question](http://stackoverflow.com/q/396748/3404097) including [this answer](http://stackoverflow.com/a/397259/3404097). – philipxy Jan 11 '16 at 09:20
  • @philipxy Thanks, problem was solved basing on Gordon Linoff's example. But still thank you for pointing me to the right direction. – Wayne Jan 11 '16 at 13:43

2 Answers2

4

You can use a bunch of nested iif() statements. An alternative is to use instr():

select *
from TableA
where [A_Design] In ("A", "D", "C" , "B")
order by instr("ADCB", A_Design);

Note: this works fine for single character codes. For longer codes, you should use delimiters:

select *
from TableA
where [A_Design] In ("A", "D", "C" , "B")
order by instr(",A,D,C,B,", "," & A_Design & ",");
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Hi Gordon Linoff, "Instr" is a very creative idea, I never thought in that direction, I will try it and feedback soon. iif() for long list could be quite messy. Thanks for your help. – Wayne Jan 11 '16 at 01:02
  • Yes, the Instr() method works. But the code can not be put after "Order By", must put after "select", then "order by 1". I have revised my question to include your example. Thanks. – Wayne Jan 11 '16 at 13:27
3

Use swich in your order by clause as shown here 

Select * From [TableA] Where [A_Design] In ("A", "D", "C" , "B")
order by switch(
     A_Design ="A", 1,
     A_Design ="D", 2,
     A_Design ="C", 3,
     A_Design ="B", 4
)

Swich is the equivalent of standard case when

Saic Siquot
  • 6,513
  • 5
  • 34
  • 56
  • Luis Siquot, I think switch() will also work for my case, but the codes will be little longer than the "Instr()" solution. Thank you very much for your prompt response. – Wayne Jan 11 '16 at 01:05