Subquery not executing Order By aggregate

Transact-SQL
1

Hello Community,

Can someone help explain why Subqueries won't execute Order By expression? Each time I try to execute the following subquery I get the error message:

Incorrect Syntax near keyword 'Order'

SELECT DISTINCT
  Stock.Color
FROM (SELECT TOP 5 PERCENT
         SalesDetails.SalesID
        ,SalesDetails.SalePrice - (Stock.Cost + Stock.PartsCost + ISNULL(Stock.RepairsCost, 0) + Stock.TransportInCost) AS myprofit
       FROM Data.SalesDetails
       INNER JOIN Data.Stock
         ON SalesDetails.StockID = Stock.StockCode
       ORDER BY myprofit) SubQuery
    ,Data.SalesDetails
     INNER JOIN Data.Stock
       ON SalesDetails.StockID = Stock.StockCode
WHERE SalesDetails.SalesID IN (SubQuery.SalesID)
2

That is by design of Ms SQL.

3

You cannot mix your JOINS this way - if you really want a CROSS JOIN then you would do this:

FROM (SELECT TOP 5 PERCENT
         SalesDetails.SalesID
        ,SalesDetails.SalePrice - (Stock.Cost + Stock.PartsCost + ISNULL(Stock.RepairsCost, 0) + Stock.TransportInCost) AS myprofit
       FROM Data.SalesDetails
       INNER JOIN Data.Stock
         ON SalesDetails.StockID = Stock.StockCode
       ORDER BY myprofit) SubQuery
     **CROSS JOIN Data.SalesDetails**
     INNER JOIN Data.Stock
       ON SalesDetails.StockID = Stock.StockCode

If you don't want a CROSS JOIN - then you need to specify the type of JOIN with the ON clause:

FROM (SELECT TOP 5 PERCENT
         SalesDetails.SalesID
        ,SalesDetails.SalePrice - (Stock.Cost + Stock.PartsCost + ISNULL(Stock.RepairsCost, 0) + Stock.TransportInCost) AS myprofit
       FROM Data.SalesDetails
       INNER JOIN Data.Stock
         ON SalesDetails.StockID = Stock.StockCode
       ORDER BY myprofit) SubQuery
     **INNER JOIN Data.SalesDetails ON {....}**
     INNER JOIN Data.Stock
       ON SalesDetails.StockID = Stock.StockCode

The ORDER BY in a derived table, view or CTE is valid when using TOP (I would say it is required - but the system will allow you to not include the ORDER BY). You cannot use an ORDER BY when not using TOP since a table (derived, virtual(view) or CTE) has no order. The ORDER BY is only used to insure you get the TOP values.

1 Like