News, views, tips and tricks on Oracle and other fun stuff

Why You Must Use an ORDER BY Sometimes

Suresh submitted this question on the Oracle Community forum:

in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?

People, it is really simple: You want an ordered result set? use ORDER BY.

Tom Kyte wrote about it in this blog post and in his books:

Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!

The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.

By the way, the query in the forum post looks suspiciously Hibernated. Don’t let Jeff know :)

Filed in Oracle on 30 Nov 11 | Tags:

Comments are closed.