Get Max from a joined table

I write this script in SQL server And I want get the food name with the Max of order count From this Joined Table . I can get Max value correct but when I add FoodName is select It give me an error.

SELECT  S.FoodName, MAX(S.OrderCount) FROM 
(SELECT FoodName,
    SUM(Number) AS OrderCount   
FROM tblFactor
INNER JOIN tblDetail
    ON tblFactor.Factor_ID = tblDetail.Factor_ID 
WHERE FactorDate = '2020-10-30'
GROUP BY FoodName)S

Here is The Error Message

Column ‘S.FoodName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

also I know I can use order by and top to achieve the food Name and Max order number but I want use the way I use in this script . Thank you for your answers

Answer

If I follow you correctly, you can use ORDER BY and TOP (1) directly on the result of the join query:

SELECT TOP (1) f.FoodName, SUM(d.Number) AS OrderCount   
FROM tblFactor f
INNER JOIN tblDetail d ON f.Factor_ID = d.Factor_ID 
WHERE f.FactorDate = '2020-10-30'
GROUP BY f.FoodName
ORDER BY OrderCount DESC

Notes:

• I added table aliases to the query, and prefixed each column with the table it (presumably !) comes from; you might need to review that, as I had to make assumptions

• If you want to allow top ties, use TOP (1) WITH TIES instead