• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

SQL ORDER BY

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?
0
Mr_Shaw
Asked:
Mr_Shaw
3 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
shru_0409Commented:
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.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
Mr_ShawAuthor Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now