join - SQL query, one record on a particular field with a group by clause

Question

I don't know if this is possible. If not, it's okay. There are certainly other ways to accomplish this. A colleague told me that this was impossible, and we have a round of drinks riding on this. No, I don't mind asking for help, but I think I'm going to be buying.

The question is: Give me a report listing the top customer in each country by the largest single order. The objective is to answer the question without subqueries, window functions, or anything other than ordinary joins.

Here is my query, so far. It's in SQLite, not that it really matters.

select o.customerid, c.companyname, c.country,
od.quantity * od.unitprice as total_order
from orders o
join order_details od on o.orderid = od.orderid
join customers c on o.customerid = c.customerid
group by  c.country
order by c.country, total_order desc;

Here are the results.

CustomerID,CompanyName,Country,total_order
OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,223.2
ERNSH,"Ernst Handel",Austria,760.0
SUPRD,"Supr?ames d??lices",Belgium,2592.0
HANAR,"Hanari Carnes",Brazil,77.0
MEREP,"M?¨re Paillarde",Canada,2000.0
SIMOB,"Simons bistro",Denmark,16.0
WARTH,"Wartian Herkku",Finland,364.8
VINET,"Vins et alcools Chevalier",France,168.0
TOMSP,"Toms Spezialit?¤ten",Germany,167.4
HUNGO,"Hungry Owl All-Night Grocers",Ireland,608.0
MAGAA,"Magazzini Alimentari Riuniti",Italy,43.2
CENTC,"Centro comercial Moctezuma",Mexico,80.0
SANTG,"Sant?? Gourmet",Norway,54.0
WOLZA,"Wolski  Zajazd",Poland,300.0
FURIB,"Furia Bacalhau e Frutos do Mar",Portugal,396.0
ROMEY,"Romero y tomillo",Spain,7.3
FOLKO,"Folk och f?¤ HB",Sweden,532.0
CHOPS,"Chop-suey Chinese",Switzerland,54.0
BSBEV,"B''s Beverages",UK,240.0
RATTC,"Rattlesnake Canyon Grocery",USA,204.0
HILAA,HILARION-Abastos,Venezuela,877.5

Here is a test query, looking ay Argentina.

select o.orderid, o.customerid, c.companyname, c.country,
od.quantity * od.unitprice as total_order
from orders o
join order_details od on o.orderid = od.orderid
join customers c on o.customerid = c.customerid
where c.country like "Argentina"
group by  c.country, o.orderid
order by c.country, total_order desc;

Here are the results of the test query.

OrderID,CustomerID,CompanyName,Country,total_order
10986,OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,630.0
10958,OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,427.0
10828,RANCH,"Rancho grande",Argentina,405.0
10937,CACTU,"Cactus Comidas para llevar",Argentina,364.8
10819,CACTU,"Cactus Comidas para llevar",Argentina,322.0
10409,OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,223.2
10881,CACTU,"Cactus Comidas para llevar",Argentina,150.0
10448,RANCH,"Rancho grande",Argentina,149.4
10531,OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,110.0
10916,RANCH,"Rancho grande",Argentina,104.7
10521,CACTU,"Cactus Comidas para llevar",Argentina,54.0
10716,RANCH,"Rancho grande",Argentina,50.0
11019,RANCH,"Rancho grande",Argentina,36.0
10898,OCEAN,"Oc??ano Atl??ntico Ltda.",Argentina,30.0
11054,CACTU,"Cactus Comidas para llevar",Argentina,25.0
10782,CACTU,"Cactus Comidas para llevar",Argentina,12.5

Obviously, the top customer in Argentina is Océano Atlántico Ltda. with an order of 630.00, but the first result shows as order of 223.2. Yeah, I can use a window function or a subquery, but the challenge is to use just a table join. Is this possible? Thanks.

question from:https://stackoverflow.com/questions/65865429/sql-query-one-record-on-a-particular-field-with-a-group-by-clause

Answer 1

If you have to aggregate the order size from the orderlines, then I don't think this is possible. The following is not a proof. It is an explanation of my thinking.

If you have the order size available in the orders table, then it should be possible.

The basic structure of that query would be:

select o.*, c.country
from orders o join
     customers c
     on o.customerid = c.customerid left join
     (orders o2 join
      customers c2
      on o2.customerid = c2.customerid
     )
     on c2.country = c.country and
        o2.ordersize > o.ordersize
where c2.country is null;  -- no bigger order in the country

However, in your problem, you need to aggregate the order size from another table. That is the problem, because you need the total order size in the ON clause, so the aggregation has to appear "before" the join. I cannot think of a method without using a subquery or CTE.

There are other methods to get the largest order, but ruling out window functions and subqueries pretty much rules them out. This is not saying that it is not possible, just that I cannot readily think of a solution due to the aggregation problem.