Robz Robz - 1 month ago 14
SQL Question

MySQL doesn't showing id_order and max()

SELECT MAX( "Total spending" ), id_order
FROM (
SELECT "id_ordine", SUM( "quantity" * "price" ) "Total spending"
FROM "t_orders", "t_products"
WHERE "t_orders"."id_products" = "t_products"."id_products"
GROUP BY "id_order"
)


This is my query: i have to show the higher shop bill of my orders and the id_order.
I have in t_orders 3 columns: id_order (we have some identical numbers because you can order more than 1 product ), id_product and quantity.
In t_products i have id_product, price and name.
My problem is that i have to show the id_order with higher shop bill and i tried to do this, but it doesn't work D:

Sorry for my bad english
PS I'm working with LibreOffice 5.2
Sorry, the column after the 2nd select is "id_order"

Answer

Use order by and limit:

SELECT "id_ordine", SUM( "quantity" * "price" ) "Total spending" 
FROM "t_orders", "t_products" 
WHERE "t_orders"."id_products" = "t_products"."id_products" 
GROUP BY "id_order"
ORDER BY "Total Spending" DESC
LIMIT 1;

Your query would not work in most databases, because id_order is not aggregated and not in a GROUP BY. MySQL accepts the syntax but just gives an order from an indeterminate row that is being processed.