TSQL Query

cousin_borat

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]

bonkey

cousin_borat wrote: »

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?

Ginger

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

DJB

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

cousin_borat

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

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.