Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

TSQL Query

Options
  • 08-05-2008 9:22am
    #1
    Registered Users Posts: 995 ✭✭✭


    Using SQL server 2005. This is a snippet from a larger stored procedure. I'm trying to insert the data into the #tmpTable in a set order.

    The data inserts in fine, however it is not inserted in descending order.

    It should be inserted in descending order on the sum(quantity) or sum(percentage).

    Will I have to write a cursor for this?
        insert into #tmpTable
          (quarters, quantity, columnName, percentage)
        select 'Annual',
               sum(quantity),
               @columnName,
               sum(percentage) sp
          from #tmpTable
         where columnName = @columnName
         [COLOR="Red"]order by sp desc[/COLOR]
    


Comments

  • Registered Users Posts: 15,443 ✭✭✭✭bonkey


    Using SQL server 2005. This is a snippet from a larger stored procedure. I'm trying to insert the data into the #tmpTable in a set order.

    The data inserts in fine, however it is not inserted in descending order.

    It should be inserted in descending order on the sum(quantity) or sum(percentage).

    Will I have to write a cursor for this?

    I'm inclined to ask why you are (or think you are) insert-order dependant.

    Given the way non-indexed tables are stored in SQL Server, you should never depend on a SELECT returning data in the same order it was INSERTed, as this is not guaranteed behaviour.

    Surely you can just as quickly sort the data when you read back from the temp tabe?


  • Registered Users Posts: 2,931 ✭✭✭Ginger


    I dont understand why it has to be inserted in order.. your SELECT afterwards will sort that.. as Bonkey points out


  • Registered Users Posts: 706 ✭✭✭DJB


    I agree... just order it in your select.


  • Registered Users Posts: 995 ✭✭✭cousin_borat


    Yeah, I was tying myself up in knots for no reason. No coffee this morning! Thanks for the replies


  • Registered Users Posts: 2,494 ✭✭✭kayos


    As said before if you need your data to be come out of the table in a certain order then add a order by to your select statement. Have seen devs depend on indexes, or the pot luck non indexed but still returning in the order they wanted "on my machine", for this before and it came back to bite them on the ass. Biggest of which was a upgrade from sql 7 to sql 2000 and the order suddenly changing and borking a complete system.


  • Advertisement
Advertisement