Display the list of records in an order in Oracle [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

SQL - order by list order

I have written a SQL query 开发者_如何学编程by passing a list of acct numbers using IN query like:-

select acctnum,acctname from account where acctnum IN ('100', '200', '300')

This is the way i am passing the acctnumbers in the query. But Oracle returns the data in the below order.

acctnum acctname 200 Bob 100 Aaron 300 Chandler

But i want the data to be displayed in the order in which i pass the acctnum (ie) display the record for the acct num "100" first followed by others. Hence, i want the result to be like :-

acctnum acctname 100 Aaron 200 Bob 300 Chandler

Is there any Oracle function we can use to display the records in the order in which it is passed? Please let me know ur opinion.

thanks.

---

AFAIK, there is no way of doing this as the method of retrieval depends upon the optimiser and the order of the rows in the table as well as other factors.

To achieve what you want to do here you'll need to explicitly order the output, something like.

SELECT acctnum,
       acctname 
  FROM account 
 WHERE acctnum IN ('100', '200', '300')
 ORDER BY (CASE acctnum
              WHEN '100' THEN 1
              WHEN '200' THEN 2
              WHEN '300' THEN 3
            END)

Hope this helps, Ollie