How to get rows ordered by desc from specific ids

user_1948089 0
Viewed 45

My goal : Getting Purchase requests ordered by most confirmed shipment locations.

Purchase requests are linked to a shipment location (warehouse).

I have a table :

CREATE TABLE IF NOT EXISTS `shipment_locations` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL
);
INSERT INTO `shipment_locations` (`id`, `name`) VALUES
  ('1', 'france'),
  ('2', 'usa'),
  ('3', 'spain'),
  ('4', 'germany');
  
CREATE TABLE IF NOT EXISTS `purchase_requests` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL,
  `total_cost_confirmed` int(6) NULL,
  `shipment_location_id` int(6) NULL,
 FOREIGN KEY (`shipment_location_id`) REFERENCES `shipment_locations` (`id`)
);

INSERT INTO `purchase_requests` (`id`, `name`, `total_cost_confirmed`, `shipment_location_id`) VALUES
  ('1', 'pr1', '109', 1),
  ('2', 'pr2', '1500', 3),
  ('3', 'pr3', '3000', 2),
  ('4', 'pr4', '10000', 2),
  ('5', 'pr5', '5', 3),
  ('6', 'pr6', '3000', 2),
  ('7', 'pr7', '3000', 2),
  ('8', 'pr8', '1', 3),
  ('9', 'pr9', '10000000', 3);

For ordering by shipment location that have the most confirmed cost, it's pretty simple :

SELECT shipment_location_id, SUM(total_cost_confirmed) totalConfirmed
FROM purchase_requests
GROUP BY shipment_location_id
ORDER BY totalConfirmed DESC

It works perfectly here : http://sqlfiddle.com/#!9/732f32/2/0

But, then I tried to filter by purchase request id (adding GROUP BY id and WHERE id IN(...)) it gives me the wrong order (because it's taking ids present in the result). => (sqlfiddle)

How I can keep the correct order from the first query while filtering by Purchase request id ?

Adding sqlfiddle : Sqlfiddle

Thanks by advance for your help :)

sql-order-by mysql
1 Answers

First aggregate to get the sum of totalConfirmed and then join to the table:

SELECT p.id, p.name, p.shipment_location_id, t.totalConfirmed
FROM purchase_requests p 
INNER JOIN (
  SELECT shipment_location_id, SUM(total_cost_confirmed) totalConfirmed 
  FROM purchase_requests
  GROUP BY shipment_location_id
) t ON t.shipment_location_id = p.shipment_location_id
WHERE p.id IN ('1', '3', '4', '8')
ORDER BY t.totalConfirmed DESC

See the demo.
Results:

> id | name | shipment_location_id | totalConfirmed
> -: | :--- | -------------------: | -------------:
>  4 | pr4  |                    2 |          19000
>  3 | pr3  |                    2 |          19000
>  8 | pr8  |                    1 |            110
>  1 | pr1  |                    1 |            110
user_104988280
Related