Posted on 2009-12-22
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
    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
    >> 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

    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 67

    Expert Comment

    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


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now