SQL Server Query returns a different result every time it is run

cgh8pdjw 于 2023-05-28 发布在其他

关注(0)|答案(4)|浏览(137)

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
DECLARE @lastStatus Varchar(10)
SELECT
    [Job].[Job],
    [Job].[Part_Number],
    [Job].[Rev],
    [Job_Operation].[Description],
    [Job].[Customer_PO],
    [Job].[Customer_PO_LN],
    [Delivery].[Promised_Date],
    [Job_Operation].[Operation_Service],
    [Job].[Note_Text],
    [Job_Operation].[Status],
    [Job_Operation].[Sequence]
INTO [#tmpTbl]
FROM [PRODUCTION].[dbo].[Job_Operation]
    INNER JOIN [Job]
        ON [Job_Operation].[Job]=[Job].[Job]
    INNER JOIN [Delivery]
        ON [Job_Operation].[Job]=[Delivery].[Job]
WHERE [Job].[Status]='Complete'
ORDER BY [Job_Operation].[Job],[Job_Operation].[Sequence]
SELECT *
FROM [#tmpTbl]
DROP TABLE [#tmpTbl]
END

sql-server

来源：https://stackoverflow.com/questions/23942649/query-returns-a-different-result-every-time-it-is-run

4条答案

按热度按时间

rkttyhzu1#

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

赞(0）回复(0）举报 2023-05-28

vzgqcmou2#

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
     INNER JOIN Job j
        ON j_o.Job = j.Job
     INNER JOIN Delivery d
        ON j_o.Job= d.Job
  WHERE j.Status='Complete'
  ORDER BY j_o.Job,j_o.Sequence
  SELECT *
    FROM [#tmpTbl]
    DROP TABLE [#tmpTbl]
  END

赞(0）回复(0）举报 2023-05-28

cnwbcb6i3#

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

Try

SELECT *
FROM [#tmpTbl]
ORDER BY Job, Sequence

赞(0）回复(0）举报 2023-05-28

k2arahey4#

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] ORDER BY ....

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server Query returns a different result every time it is run

4条答案

相关问题

热门标签

最新问答