ORDER BY PROBLEM 10G

Hi,

I have a sort  problem  my queries on 10g.

venomx_Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
As mentioned, the only way to guarantee a specific order is use 'order by'.

You also need to take into account an ASCII sort and number sort.
0
 
slightwv (䄆 Netminder) Commented:
I'm afraid we'll need a little more information before we can respond.

Maybe the queries, a brief description of the problem?  Something.
0
 
venomx_Author Commented:
I used to 8.1.5 i didn't have order by problem but I  use 10g I have a peroblem. Query output did not order by sorting. is this optimizer problem?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MrNedCommented:
Can you provide an example query? There were changes in Oracle's GROUP BY algorithm which no longer produced sorted output, but it was never guaranteed to anyway without an ORDER BY.
0
 
slightwv (䄆 Netminder) Commented:
Please post query.

Does the query have an order by?  
What are the columns that aren't being sorted properly?  
What changed?
0
 
venomx_Author Commented:
select x from yyy group by xx;

xx
----------
3513
353
611
583
631


select x from yyy group by xx order by xx;


xx

-------

011
012
014
015
016
021
032
044
051
0
 
SharathData EngineerCommented:
There is no ordering between the records in a table. You have to provide the ORDER BY clause to display the result set in an order. Otherwise there is no guarantee that records will display in same order every time.
0
 
slightwv (䄆 Netminder) Commented:
Sharath_123,

Thanks for the post however this has already been mentioned twice before.  Please review previous posts before posting.
0
 
LFLFMCommented:
Your query seems to be sorted just fine, but here are some considerations:
1. sorting character fields containing number may not sort exactly the way you want or expect, experiment with your results
2. if you want to sort backwards, use the DESC keyword after the field name, in this example, your fieldname is xx: "sort by xx desc"
3. If you have a character field that REALLY contains ONLY numbers and you want to sort as if they were numbers, you can use to_number(xx).... "order by to_number(xx)"

If none of what I said helps you, then you really need to post the problem that you are having and a real example query that actually shows your problem. :-)
0
 
slightwv (䄆 Netminder) Commented:
>>Your query seems to be sorted just fine,

It's been posted a lot:  The only way to ensure an order of data in Oracle is with an "ORDER BY".
0
 
LFLFMCommented:
>>It's been posted a lot:  The only way to ensure an order of data in Oracle is with an "ORDER BY".

Mmm.. I never told him to use order by... he is already using it and seems to be complaining that its not working the way he wants to; (except he didn't explain what, exactly, his problem is) so I sugested ways in which to use his order by's...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.