Dim Team Dim Team -4 years ago 95
SQL Question

Improve query of getting row with max value

I have next tables:

CREATE TABLE IF NOT EXISTS `Customers` (
`id` INT AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `Orders` (
`id` INT AUTO_INCREMENT,
`id_cust` INT NOT NULL,
`descr` VARCHAR(40),
`price` INT NOT NULL,
PRIMARY KEY(`id`),
FOREIGN KEY(`id_cust`) REFERENCES `Customers`(`id`)
);


One customer can have many orders. I want to get
id_cust
and sum of the orders of who paid the most(one person).
My query:

SELECT cust, max_orders_sum
FROM
(
(
SELECT MAX(orders_sum) AS max_orders_sum
FROM (
SELECT o.id_cust AS cust, SUM(o.price) AS orders_sum
FROM Orders AS o
GROUP BY o.id_cust
) AS same_query0
) AS step1
INNER JOIN
(
SELECT o.id_cust AS cust, SUM(o.price) AS orders_sum
FROM Orders AS o
GROUP BY o.id_cust
) AS same_query1
ON (step1.max_orders_sum = same_query1.orders_sum)
);


Main problem:
as you can see, it has the same parts:
same_query0
and
same_query1
. Is there any way to get rid of them?
Or if you know the better way to reach my goal, please share.
I found one simple solution:

SELECT o.id_cust AS cust, SUM(o.price) AS orders_sum
FROM Orders AS o
GROUP BY o.id_cust
ORDER BY orders_sum DESC LIMIT 1;


But this is not a direct way to solve the problem.

Answer Source

Another one nice solution:

select id_cust, sum(price) from orders group by id_cust having sum(price) = 
(select max(prc) from
(select sum(price) as prc from orders group by id_cust) as tb);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download