Results 1 to 3 of 3

Thread: Order by Issue

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Order by Issue

    I am having a small issue with the order by command.

    Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

    select *
    from [table1]
    order by column_a, column_b, column_c

    My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

    select *
    into [new table]
    from [table1]
    order by column_a, column_b, column_c


    My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

    Any help would be appreciated. It is driving me crazy!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Data in a recordset has no order unless specified. While you may have specified an order when you inserted the records, there is no guarantee that they will be maintained in that order, or returned in that order.

    You can ensure that they are stored in a specific order by placing a clustered index on the columns you want, and MOST of the time queries against the table will return the records in that order, but again there is no guarantee.

    You should always issue an ORDER BY clause on all SQL Statements which require data to be returned in a specified order.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The only time the resultset will be in the order of the clustered index is if you run your SELECT on a single processor machine, or if you set only 1 processor for SQL Server service, or if you use OPTION (MAXDOP 1). In any other case the order of the clustered index does not affect the order of the resultset without ORDER BY.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •