Problem solve Get help with specific problems with your technologies, process and projects.

SQL query with GROUP BY clause in Oracle 8i vs. Oracle 10g

I have a SQL query using a GROUP BY clause and no ORDER BY clause. The results are different in Oracle 8i and Oracle 10g.

I have one SQL query using a GROUP BY clause and no ORDER BY clause is used. When executed in Oracle 8i, the query results are returned ordered by first column mentioned in the GROUP BY clause. When the same query is executed in Oracle 10g, the query results are returned withour ordering the data by the first column in the GROUP BY clause. It works only when I explicitly mention the ORDER BY clause. Can you please explain this? In Orcale 8i, is it that, by default, the data is ordered by the first column mentioned in the GROUP BY clause when ORDER BY clause is not mentioned?
Oracle never guarantees the ordering of your result set unless you include an ORDER BY clause. You are seeing a change in the algorithm of how Oracle internally codes the GROUP BY clause between the versions. This can happen between versions. So if you want your data sorted, include the ORDER BY clause.
This was last published in April 2007

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.