retrieve resultset based on where clause condition order in sql server

Consider the following table

ColID  Value
  A     100
  B     200
  C     300
  D     400
  E     500

What is the query for retrieving the values f开发者_如何学JAVAor colID 'C','A','D' in the same order?

For that particular ordering you would have to do something like:

SELECT * FROM MyTable WHERE ColID IN ('C', 'A', 'D') 
   ORDER BY 
      CASE ColID 
         WHEN 'C' THEN 0 
         WHEN 'A' THEN 1 
         ELSE 2 
      END

How about using CASE?

SELECT [Value]
FROM testTable
WHERE colID IN ('C','A','D')
ORDER BY (CASE colID WHEN 'C' THEN 1 WHEN 'A' THEN 2 WHEN 'D' THEN 3 END);

Select * from Tablex Where ColId in ('C', 'A', 'D') order by ColId  -- This will yield 'A', 'C', 'D' order

If you needed it to return in 'C' 'A' 'D' order, you would need to have another column like:

ColID Value  Ordinal
A        100    2
B        200    5
C        300    1
D        400    3
E        500    4

Then do:

Select * from Tablex Where ColId in ('C', 'A', 'D') order by Ordinal

You can do something like this:

select * from your table Where ColId in ('C', 'A', 'D')

As for that particular order, you can't sort by either ColId or Value. An order by will not give it to you in that order.