Query Returns A Different Result Every Time It Is Run

This query always returns the same amount of rows but, in a different order, every time. Why does this happen? I have more filters to add but I can't get past this step. BEGIN DECL

Solution 1:

Put the Order By on the Select * From #tmpTbl, not on the insert.

Solution 2:

Hi you can do initials on your table and you can remove your bracket for non spaces so you can make your code shorter.

SELECT  j.Job,
       ,j.[Part_Number]
       ,j.Rev
       ,j_O.Description
       ,j.Customer_PO
       ,j.[Customer_PO_LN]
       ,d.[Promised_Date]
       ,j_o.[Operation_Service]
       ,j.[Note_Text],
       ,j_o.Status,
       ,j_o.Sequence
       ,j.[Customer_PO],
       ,j.[Customer_PO_LN],
       ,d.[Promised_Date],
       ,j_o.[Operation_Service],
       ,j.[Note_Text],
       ,j_o.[Status],
      [Job_Operation].[Sequence]
      INTO [#tmpTbl]
  FROM [PRODUCTION].[dbo].[Job_Operation] j_o
     INNERJOIN Job j
        ON j_o.Job = j.Job
     INNERJOIN Delivery d
        ON j_o.Job= d.Job
  WHERE j.Status='Complete'ORDERBY j_o.Job,j_o.Sequence
  SELECT*FROM [#tmpTbl]
    DROPTABLE [#tmpTbl]
  END

Solution 3:

Because you don't have an order by clause when you select from #tmpTbl

Try

SELECT *
FROM [#tmpTbl]
ORDERBY Job, Sequence

Solution 4:

You cannot specify the order data goes into a table through a SET command (i.e. SELECT INTO) - that is determined by whether the table has a clustered index defined after it's created.

You control the order of the data when you're eventually selecting FROM that table to get your results.

SELECT * FROM [#tmpTbl] ORDERBY ....