asked
Why this SQL statement does not give always same result
Why this SQL statement does not give always same result? when you click execute first and second time it gives same results, but when you repeat clicking execute couple of times, you will noticed that results will be other than first time when you clicked execute??? SELECT TOP (10) SalesPOSHeadersShift.CompanyId, SalesPOSHeadersShift.OrderYear, SalesPOSHeadersShift.DocumentId, SalesPOSHeadersShift.OrderId, SalesPOSHeadersShift.WarehouseId, SalesPOSHeadersShift.OrderDate, SalesPOSHeadersShift.OrderDatePayment, SalesPOSHeadersShift.OrderDateShipment, SalesPOSHeadersShift.PartnerId, SalesPOSHeadersShift.Document1Value, SalesPOSHeadersShift.Document1Date, SalesPOSHeadersShift.Document2Value, SalesPOSHeadersShift.Document2Date, SalesPOSHeadersShift.TariffTypeId, SalesPOSHeadersShift.Back, SalesPOSHeadersShift.Finished, SalesPOSHeadersShift.UserId, SalesPOSDocuments.DocumentName, Warehouses.PartnerName AS WarehouseName, Partners.PartnerName, Total.SupplierBrutoValue, Total.BuyerNetoValue, Total.BuyerBrutoValue, Total.BuyerDiscountValue, Users.UserFullName FROM (SELECT SalesPOSHeadersShift.CompanyId, SalesPOSHeadersShift.OrderYear, SalesPOSHeadersShift.DocumentId, SalesPOSHeadersShift.OrderId FROM SalesPOSHeadersShift GROUP BY SalesPOSHeadersShift.CompanyId, SalesPOSHeadersShift.OrderYear, SalesPOSHeadersShift.DocumentId, SalesPOSHeadersShift.OrderId HAVING (SalesPOSHeadersShift.CompanyId = 1) AND (SalesPOSHeadersShift.DocumentId = 100 OR SalesPOSHeadersShift.DocumentId = 101) EXCEPT SELECT TOP (0)SalesPOSHeadersShift.CompanyId, SalesPOSHeadersShift.OrderYear, SalesPOSHeadersShift.DocumentId, SalesPOSHeadersShift.OrderId FROM SalesPOSHeadersShift GROUP BY SalesPOSHeadersShift.CompanyId, SalesPOSHeadersShift.OrderYear, SalesPOSHeadersShift.DocumentId, SalesPOSHeadersShift.OrderId HAVING (SalesPOSHeadersShift.CompanyId = 1) AND (SalesPOSHeadersShift.DocumentId = 100 OR SalesPOSHeadersShift.DocumentId = 101)) AS Results INNER JOIN SalesPOSHeadersShift ON Results.CompanyId = SalesPOSHeadersShift.CompanyId AND Results.OrderYear = SalesPOSHeadersShift.OrderYear AND Results.DocumentId = SalesPOSHeadersShift.DocumentId AND Results.OrderId = SalesPOSHeadersShift.OrderId INNER JOIN TotalSalesPOSShift() AS Total ON SalesPOSHeadersShift.CompanyId = Total.CompanyId AND SalesPOSHeadersShift.OrderYear = Total.OrderYear AND SalesPOSHeadersShift.DocumentId = Total.DocumentId AND SalesPOSHeadersShift.OrderId = Total.OrderId INNER JOIN SalesPOSDocuments ON SalesPOSHeadersShift.CompanyId = SalesPOSDocuments.CompanyId AND SalesPOSHeadersShift.DocumentId = SalesPOSDocuments.DocumentId INNER JOIN Partners AS Warehouses ON SalesPOSHeadersShift.WarehouseId = Warehouses.PartnerId INNER JOIN Partners ON SalesPOSHeadersShift.CompanyId = Partners.CompanyId AND SalesPOSHeadersShift.PartnerId = Partners.PartnerId INNER JOIN Users ON SalesPOSHeadersShift.UserId = Users.UserId
`SELECT` doesn't always return data in the same order. The only way to guarantee the order in which results are returned is to include an `ORDER BY` clause. This is applied to the resultset before the `TOP` clause is used to take the first 10 rows.
