Default row ordering for select query in oracle

oracle sql-order-by
sengs picture sengs · May 22, 2009 · Viewed 81k times · Source

In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified.

Is it

  1. the order in which the rows were inserted
  2. there is no default ordering at all
  3. none of the above.

Answer

DCookie picture DCookie · May 22, 2009

According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."

See this question at asktom.com.

As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.

@ammoQ: you might want to read this AskTom article on GROUP BY ordering. In short:

Does a Group By clause in an Query gaurantee that the output data will be sorted on the Group By columns in order, even if there is NO Order By clause?

and we said...

ABSOLUTELY NOT,

It never has, it never did, it never will.

Related questions

How to use Oracle ORDER BY and ROWNUM correctly?

I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it. I have queries which returns the most recent record of some tables, based on a timestamp : SQL Server: SELECT …

Read More
Custom Order in Oracle SQL

I need to order transaction based on the currency. However I need to implement a custom order by, which makes the USD to always comes on the top, and the rest should be ordered asc. for example : BHT USD MYR …

Read More
SQL (ORACLE): ORDER BY and LIMIT

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET. Query like this: SELECT select_list FROM table_expression [ ORDER BY ... ] [ LIMIT { number | ALL } ] [ OFFSET number ] I know the sorting ends …

Read More