Professional Web Applications Themes

Ordered Data Inserted into TEMP table, is Read out in Different ORDER?? - Microsoft SQL / MS SQL Server

Im using a query with OrderBy clause to populate a temp table. The Query works and Order and everything is Fine But when reading the data out of the temp table, some of the rows are positioned wrongly??? ie in the wrong order 75% of the ordering seems fine but a few rows are in the wrong places Insert Into #temptbl Select col1, col2, col3 From tbltest Group By col1, col2, col3 Order by col1, col2, col3 Select * from #temptbl Any Ideas... i thought temp tables will return rows via select statment in the same order as the original ...

  1. #1

    Default Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    Im using a query with OrderBy clause to populate a temp table. The Query
    works and Order and everything is Fine
    But when reading the data out of the temp table, some of the rows are
    positioned wrongly??? ie in the wrong order
    75% of the ordering seems fine but a few rows are in the wrong places

    Insert Into #temptbl
    Select col1, col2, col3
    From tbltest
    Group By col1, col2, col3
    Order by col1, col2, col3

    Select * from #temptbl



    Any Ideas... i thought temp tables will return rows via select statment in
    the same order as the original query used to insert the rows into the temp
    table???


    RKD Guest

  2. #2

    Default Re: Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    You have to use an Order By on the select to guarantee the correct order.
    There is no exception to this. The order by on the insert is pretty much
    useless in this scenario.

    --

    Andrew J. Kelly
    SQL Server MVP


    "RKD" <ragubir_28hotmail.com> wrote in message
    news:2fUOa.13303$La.6351news-binary.blueyonder.co.uk...
    > Im using a query with OrderBy clause to populate a temp table. The Query
    > works and Order and everything is Fine
    > But when reading the data out of the temp table, some of the rows are
    > positioned wrongly??? ie in the wrong order
    > 75% of the ordering seems fine but a few rows are in the wrong places
    >
    > Insert Into #temptbl
    > Select col1, col2, col3
    > From tbltest
    > Group By col1, col2, col3
    > Order by col1, col2, col3
    >
    > Select * from #temptbl
    >
    >
    >
    > Any Ideas... i thought temp tables will return rows via select statment in
    > the same order as the original query used to insert the rows into the temp
    > table???
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    But why is this,
    isnt the data written into the temp table in the order its inserted?

    This is really wierd

    Thanks for Replying

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:OCgUnphRDHA.2768tk2msftngp13.phx.gbl...
    > You have to use an Order By on the select to guarantee the correct order.
    > There is no exception to this. The order by on the insert is pretty much
    > useless in this scenario.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "RKD" <ragubir_28hotmail.com> wrote in message
    > news:2fUOa.13303$La.6351news-binary.blueyonder.co.uk...
    > > Im using a query with OrderBy clause to populate a temp table. The Query
    > > works and Order and everything is Fine
    > > But when reading the data out of the temp table, some of the rows are
    > > positioned wrongly??? ie in the wrong order
    > > 75% of the ordering seems fine but a few rows are in the wrong places
    > >
    > > Insert Into #temptbl
    > > Select col1, col2, col3
    > > From tbltest
    > > Group By col1, col2, col3
    > > Order by col1, col2, col3
    > >
    > > Select * from #temptbl
    > >
    > >
    > >
    > > Any Ideas... i thought temp tables will return rows via select statment
    in
    > > the same order as the original query used to insert the rows into the
    temp
    > > table???
    > >
    > >
    >
    >

    RKD Guest

  4. #4

    Default Re: Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    >> But why is this, isnt the data written into the temp table in the order
    its inserted? <<

    No, tables by definition are logically an unordered set of rows. Using ORDER
    BY in a INSERT...SELECT or SELECT...INTO does not mean that the data in the
    table is 'ordered'. The only guaranteed way of retrieving rows in a specific
    order is to use an ORDER BY clause in your SELECT statement.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  5. #5

    Default Re: Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    Yes, I knew that was the case with permanent tables
    but for some reason always thought temp tables were in memory..... so u
    could play with the order like u do with lets say ADO RecordSets

    Thanks for all the info


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:eVJqi1hRDHA.1804TK2MSFTNGP11.phx.gbl...
    > >> But why is this, isnt the data written into the temp table in the order
    > its inserted? <<
    >
    > No, tables by definition are logically an unordered set of rows. Using
    ORDER
    > BY in a INSERT...SELECT or SELECT...INTO does not mean that the data in
    the
    > table is 'ordered'. The only guaranteed way of retrieving rows in a
    specific
    > order is to use an ORDER BY clause in your SELECT statement.
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    RKD Guest

  6. #6

    Default Re: Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

    In case of temp table check out if your tempdb is split into more than one and its size. That hurts the order even if you put order by.
    ajit.singh Guest

Similar Threads

  1. Horizontal Scroll appears after 2nd Table inserted
    By wubba in forum Macromedia Dynamic HTML
    Replies: 4
    Last Post: April 14th, 04:17 PM
  2. Retrieving ID of Last Inserted Table
    By JimCu in forum Coldfusion Database Access
    Replies: 5
    Last Post: February 8th, 11:56 PM
  3. Pages are inserted out of order.
    By Ron_Hare@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: May 12th, 11:52 PM
  4. Replies: 9
    Last Post: June 28th, 06:04 PM
  5. selecting ordered groups out of a table
    By Ian D Bjorhovde in forum IBM DB2
    Replies: 0
    Last Post: August 30th, 01:57 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139