ERROR: 'Incorrect syntax near the keyword 'ORDER'.'-CodePudding

I'm getting an error from my SQL query:

SELECT TOP 20 * FROM 
    (
      SELECT DISTINCT
         p.ItemGroupName, p.Varenummer, s.EAN, s.inventoryQuantity 
      FROM 
        ShopInventory s, ProductData p
     WHERE s.EAN = p.EAN
) 
ORDER BY cast(inventoryQuantity AS int) DESC

ERROR: 'Incorrect syntax near the keyword 'ORDER'.'

CodePudding user response：

Probably, you just need to give the subquery an alias:

SELECT TOP 20 * FROM 
    (
      SELECT DISTINCT
         p.ItemGroupName, p.Varenummer, s.EAN, s.inventoryQuantity 
      FROM 
        ShopInventory s, ProductData p
     WHERE s.EAN = p.EAN
) mytable
ORDER BY cast(inventoryQuantity AS int) DESC

Some would say you are using the old join syntax instead of the recommended JOIN clause but for the purposes of solving your question I think thats a bit of a distraction. If you're interested in INNER JOIN , OUTER JOIN and all that you can read up here: What is the difference between "INNER JOIN" and "OUTER JOIN"?