I have a Postgresql database where I have these two tables.
shipping_method
id | name | abbrev ---+----------+------- 1 | Standard | ST 2 | Express | EX
shipping_details:
| id | shipping_method_id | estimated_time_min | estimated_time_max | price |
|---|---|---|---|---|
| 2 | 1 | 02:00:00 | 04:00:00 | 230 |
| 3 | 2 | 00:03:00 | 01:00:00 | 500 |
| 4 | 1 | 02:00:00 | 04:00:00 | 1230 |
| 5 | 1 | 02:00:00 | 04:00:00 | 850 |
| 6 | 2 | 01:00:00 | 02:00:00 | 1785 |
My goal is to fetch the most expensive shipping details per shipping method (for a specific product [not in OP]).
So far, I wrote this query:
SELECT
sm.id, sm.name, MAX(sd.price) AS max_price
FROM
shipping_details AS sd
LEFT JOIN
shipping_method AS sm ON sm.id = sd.shipping_method_id
GROUP BY
sm.id
which returns:
id | name | max_price ---+----------+--------- 2 | Express | 1785 1 | Standard | 1230
Through that query, I am not able to get the shipping_details columns without putting them in GROUP BY clause. I mainly need shipping details for each specific shipping method that has the higher price.
How can I achieve that?
Advertisement
Answer
Use DISTINCT ON:
SELECT DISTINCT ON (sm.id) sm.id, sm.name, sd.price AS max_price
FROM shipping_details AS sd
LEFT JOIN shipping_method AS sm
ON sm.id = sd.shipping_method_id
ORDER BY sm.id, sd.price DESC;
The above logic will return the shipping method having the max price.