• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

How to sort while inserting

Hello experts,

I have a database app with many tables and stored procs. Some stored procedures do insert into tamporary tables. My insert statements also include select statements. When I select I use order by clause but after insert, the rows are not ordered by the column name in order by clause. Any idea?

Thanks
0
sunny012097
Asked:
sunny012097
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you query the temp tables without ORDER BY clause, they are never guaranteed to come out "ordered" by the select.
you have to apply a ORDER BY when selecting from the temp table also
0
 
sunny012097Author Commented:
Here is a sample query from a stored procedure:
I want rows to be ordered by SessionDate which correspons to day column in the report_montly_value table

            
      INSERT INTO report_monthly_value (day,time_scale,inv_cost_val,inv_retail_val)
                  SELECT ins.SessionDate, RIGHT(CONVERT(char(9), ins.SessionDate, 6), 6), SUM(ind.ItemCOST * ind.ItemQTY), SUM(ind.ItemRETAIL * ind.ItemQTY)
                  FROM Inv_Data as ind, Inv_Sessions as ins
                  WHERE ins.ClientID  = @client_id AND
                        ins.ID = ind.SessionID AND
                        ind.InventorySTATUS = 'POST'
                  GROUP BY ins.SessionDate
                  ORDER BY ins.SessionDate
0
 
DireOrbAntCommented:
sunny, don't worry about insert order, let SQL take care of that internally.

If you want to see the results ordered, then do this:
SELECT day,time_scale,inv_cost_val,inv_retail_val
FROM report_monthly_value
WHERE -- Whatever you need to filter on here
ORDER BY day

And just like angelIII posted, NEVER assume the order will be what you want, add an ORDER BY clause.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sunny012097Author Commented:
I have some reports that use the data in tables and they down't pull the data sorted. I don't want to fix all the reports one by one.
is there any way that I can order the rows in a table with a command?
0
 
sunny012097Author Commented:
I want to sort the rows somehow without select...
0
 
DireOrbAntCommented:
I guess if you make day the Clustered Index on report_monthly_value, it might order them properly.
As a plus, your queries, if based on date range, will run faster.

Now, you understand that this is not a good practice, might not work, or ordering might change at any time :)
0
 
Anthony PerkinsCommented:
>>I want to sort the rows somehow without select...<<
As DireOrbAnt has pointed out already you can hope that using a Clustered Index will always return the results in the right order and that by the time that changes you have long since moved on to better pastures or you can do it right.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now