Changing the order of results

[TStriker]

Hello all, I'm running Oracle 8i and I have a simple (?) question.

I have a query like this:

SELECT words FROM table WHERE words IN ('My','Dog','Has','Fleas');

It returns this:

Fleas
Has
Dog
My


Of course I would like to have it return this:

My
Dog
Has
Fleas

Is there an easy way to have Oracle return the results in the same order that the values were entered?

-Striker

 

[SantaMufasa]

First of all, TStriker, You can NEVER rely upon the "entry order" of data in an Oracle table. Although you can often predict with a fair amount of assurance, the order of rows in a never-before-populated table, you cannot rely at all on the order of rows after there have been UPDATEs, DELETEs, and INSERTs of row data, since Oracle re-uses and re-organises space consumption based upon its own internal algorithms.

Now, referring back to your original issue of attempting to sort by some algorithm that does not match any native colating sequence, you can "fool" Oracle by overriding natural colating sequences with your own sequence, by using this method:

select words from striker;

WORDS
---------
Yada
Fleas
Dog
Yada Yada
Has
My
Whatever

SELECT words FROM striker WHERE words IN ('My','Dog','Has','Fleas')
order by decode(words
               ,'My',1
               ,'Dog',2
               ,'Has',3
               ,'Fleas',4);

WORDS
----------
My
Dog
Has
Fleas

Let us know if you have questions about any of this.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Thanks Dave. The query is being performed within an .asp page so VBScript was a handy and available tool in this particular situation. I was just hoping that Oracle had a native function to reverse the order.

Message understood on the unreliability of just returning results without specifying a sort order. In this case the desired order is set by the user based on the sequence in which they enter the query parameters.

-Striker

 

[Beilstwh]

If the word table has a sequence number then you could do something like.

SELECT words FROM striker WHERE words IN ('My','Dog','Has','Fleas')
order by seq desc;

Bill
Oracle DBA/Developer
New York State, USA