You need to get Supplier.Name the weight of all goods which is maximum.

SELECT Name FROM ( SELECT Name, SUM(Weight) AS Sum_weight FROM Supplier S, Product P, P_S_Connect PS WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID GROUP BY Name ) NewTable WHERE Sum_weight=(SELECT MAX(Sum_weight) FROM NewTable);

Gives an error message

Msg 208, Level 16, State 1, Line 31 Invalid object name "NewTable".

    2 answers 2

    NewTable is an alias of a selection in a particular query; it cannot be accessed again as a table.

    You can do this for example using CTE:

     WITH NewTable(Name,Sum_weight) as( SELECT Name, SUM(Weight) FROM Supplier S, Product P, P_S_Connect PS WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID GROUP BY Name ) SELECT Name FROM NewTable WHERE Sum_weight=(SELECT MAX(Sum_weight) FROM NewTable);

    Or so, using window functions:

     SELECT Name FROM ( SELECT Name, Sum_weight, MAX(Sum_weight) over() as Max_weight FROM ( SELECT Name, SUM(Weight) AS Sum_weight FROM Supplier S, Product P, P_S_Connect PS WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID GROUP BY Name ) A ) A WHERE Sum_weight=Max_weight

    And on standard SQL, without CTE extensions or window functions, I’m afraid only like this:

     SELECT Name FROM Supplier S, Product P, P_S_Connect PS WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID GROUP BY Name HAVING SUM(Weight)= ( SELECT MAX(Sum_weight) FROM ( SELECT SUM(Weight) AS Sum_weight FROM Supplier S, Product P, P_S_Connect PS WHERE S.SupplierID=PS.SupplierID AND P.ProductID=PS.ProductID GROUP BY Name ) A )
    • Thank you, did not know. But is it possible to make this a query with a subquery? - salkcid
    • one
      @Viktor In principle, it is possible, but then you will have to completely repeat the request itself in a subquery, it will not work to refer to - Mike
    • It turns out that you need to name the table that is created in the subquery? Indeed, without this alias, it produces the error Msg 102, Level 15, State 1, Line 61. The incorrect syntax around the ")". - salkcid
    • one
      @Viktor In MS Sql yes, the sample must have an alias of from. Some other DBMSs do without it - Mike
    • Thank you, problem solved. - salkcid

    For the future., The order of execution of any request: From -> Where -> Group by -> Having -> Select -> Order by

    • It certainly is, But how it will help understanding the essence of the problem. If you go along such a chain, then the logic would personally tell me: "the first was executed from the main query, so in where you can already use the name NewTable", and yes, they really can use it there, but you see, not in the subqueries as a table - Mike