0

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'.'

philipxy
  • 14,867
  • 6
  • 39
  • 83
Andreas Ravnholt
  • 49
  • 7

1 Answers1

-1

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"?

codeulike
  • 22,514
  • 29
  • 120
  • 167
  • This does sort of fix it, however, i would like for there not to be replicas of p.itemgroupname and p.varenummer and so on, i thought distinct would remove that, but it doesnt seem to do that – Andreas Ravnholt Oct 06 '22 at 11:38
  • 1
    The distinct would be accross all four fields, is that what you're getting? i.e. each combination of ItemGroupName, Varenummer, EAN and inventoryquantity should only occur once – codeulike Oct 06 '22 at 11:40
  • No, right now the only distinct element seems to be s.EAN but the 3 other fields arent distinct – Andreas Ravnholt Oct 06 '22 at 11:42
  • 1
    @AndreasRavnholt Ask a new (specific researched non-duplicate on-topic) question in a new post. [ask] [Help] – philipxy Oct 06 '22 at 11:42
  • Distinct might not be what you needed. You might want to SUM the inventoryQuantity over one or more of the other fields. – codeulike Oct 06 '22 at 11:46