Does group by or distinct force a sort? 2

[cmmrfrds]

I have fiscal year as part of my time dimension table and would like the years to be retrieved in order.

2000
2001
2002
.
.

Here is the query
Select
TIME_FISCAL_YEAR
From Clarity.STATIC_TIME_DIMENSION
Group By TIME_FISCAL_YEAR

The years are being retrieved.

2005
2003
2012
.
.

This is not the order I expected. I don't have control over this query since it is done by a Business Objects product when is generates a pick list for one of it's tools. Is there a way (setting) to have Oracle sort a distinct or group by in order by the field value? Oracle Version 10G.

 

[SantaMufasa]

cmmrfrds,

Although Oracle may be using sort algorithms internally to achieve GROUP BY or DISTINCT results, Oracle recommends that you do not design applications based upon empirical observations about sort order from GROUP BY or DISTINCT operations.

If you want data in a particular order, then explicitly use the ORDER BY clause. (And if your third-party interface software doesn't allow ORDER BY, then that is the software's deficiency, not Oracle's.)

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

 

[jimirvine]

What business Objects product? Any of the reporting tools (from any version) gives you sorting capabilities within the application, and I'm pretty sure that some of the full client versions allow you to push down the sort to the db via the query panel.

 

[cmmrfrds]

The product is WebI Intelligence XIR2 document built on a Business Objects Universe. To paraphrase the documentation it says that when using a field as a Filter that the product does a Select Distinct on the field to produce the pick list. I can't find any information that shows I can sort the list produced that is why I was checking to see if Oracle could do this without an Order By Clause. I will call their tech support to see if some property can be set to produce the sort. Thank you.

 

[dbtoo2001]

Ask BOB -

http://www.forumtopics.com/busobj/index.php

I beleive the LOV can be sorted in the universe.

 

[Beilstwh]

In the previous versions of Oracle, a group by or distinct would normally return a sorted list. However, oracle uses a faster sorting method called hashing that will not guarantee any order in the result set.

Bill
Oracle DBA/Developer
New York State, USA

 

[cmmrfrds]

There is a property in the Universe that can be set top force a sorted list. I found the info on the bosobj Forum.
Thank you. Also, I appreciate the response on how Oracle does the Distinct and Group By.

 

[sem]

Of course, previous, but more exactly non-parallel versions.

Even Oracle 7 was capable to produce non-sorted group-by results. In any case you should not rely upon this order.

Regards, Dima

http://seminihin.narod.ru