Post reply

strange issue with group by and order by after insert

  • December 1, 2011 at 11:33 am

    #263919

    INSERT INTO Product_Date

    SELECT Fecha, Producto, sum(Cantidad)

    FROM #temp

    Group by Fecha, producto

    Order by Fecha

    If I comment the "insert.." line it shows perfectly ordered (as I want)

    but if I uncomment the insert and later make a: select * from Product_Date I dont get

    the same result (there are some disorder, disarray (sorry for my bad english))

    Is that expectable? I want it ordered in the final table.

    Thanks for any help.

  • December 1, 2011 at 11:55 am

    #1415488

    SQL doesn't store the data in any "ORDER". The engine determines the best place/way to store the data on it's own. Regardless of how you insert it.

    It would only apply if there were an identity column on the table and you would still need to ORDER BY the identity column when selecting the data.

    Make sense?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • December 1, 2011 at 12:26 pm

    #1415512

    Tables, by definition, don't have an order. The only thing that order by on an insert will do is control how identity values are assigned. If you want a resultset ordered, you have to put an order by in the final SELECT. Otherwise there is no guarantee of any ordering at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2011 at 12:39 pm

    #1415517

    GilaMonster (12/1/2011)

    Tables, by definition, don't have an order. The only thing that order by on an insert will do is control how identity values are assigned. If you want a resultset ordered, you have to put an order by in the final SELECT. Otherwise there is no guarantee of any ordering at all.

    That sounds oddly familiar. :hehe:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • December 2, 2011 at 12:09 pm

    #1416151

    thanks to both, it's clear now.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply