Find the location with most items-CodePudding

Compute the most expensive box in each location.

But one location has a different id but the same location name

SELECT s.Location, p.Value, p.Warehouse, s.Code 
FROM Warehouses s JOIN
     Boxes p
     ON s.Code = p.Warehouse AND
        p.Value >= (SELECT MAX(p2.Value)
                    FROM Boxes p2, Warehouses s2
                    WHERE p2.Warehouse = s.Code
                   );

I need this result:

Location    Value    
Chicago           250     
New York      180     
Los Angeles   190    
San Francisco      90

but I get this

Location    Value   Warehouse_id  Warehouse_id_box   
Chicago           250       1           1
Chicago       175       2           2
New York      180       3           3
Los Angeles   190       4           4
San Francisco      90       5       5

CodePudding user response：

I think this should do it.

SELECT w.Location, MAX(COALESCE(b.value,0)) AS "Most Expensive Box"
FROM Warehouses w 
LEFT JOIN Boxes b ON w.Code = b.Warehouse
GROUP BY w.Location;

It doesn't matter if there are two different codes for Chicago. That just means there are (at least) two warehouses in Chicago. The question is asking what's the most expensive box by location. So, among all warehouses in each city. The question is not asking for most expensive by warehouse.