Posted on 2009-12-22
Medium Priority
Last Modified: 2012-05-08
I have a written a query without putting an ORDER BY.

I noticed that every time I ran the query the order of the records would change.

I have now placed an ORDER BY in the query and the order is now the same every time it is run.

Is it normal for the order to change each time a query is executed if it does not have an ORDER BY?
Question by:Mr_Shaw
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 640 total points
ID: 26104384
Its perfectly possible that the order will change. Not specifying an ORDER BY basically leaves it up to the database engine to decide what order to return the records in.
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 640 total points
ID: 26104449
>> Is it normal for the order to change each time a query is executed if it does not have an ORDER BY?

Yes.. It's normal.
If you require ORDERED results, then its mandatory to include ORDER BY clause in yoru query..
LVL 14

Accepted Solution

shru_0409 earned 720 total points
ID: 26104502
ORDER BY clause - is used to order rows returned by the statement. Without an order_by_clause, no guarantee exists that
the same query executed more than once will retrieve rows in the same order.
Restrictions on the order_by_clause:
w If you have specified the DISTINCT operator in this statement, this clause cannot refer to columns unless they appear
in the select list.
w An order_by_clause can contain no more than 255 expressions.
w You cannot order by a LOB column, nested table, or varray.
LVL 71

Expert Comment

ID: 26104526
To be precise, the behaviour without ORDER BY is not defined. The DBMS can retrieve records depending on how it gets them: via indexes, materialized views, memory, disk, physical storage and access abilities, aso.

Author Closing Comment

ID: 31668955

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question