PeterH PeterH - 7 months ago 11
SQL Question

mysql - find minimum value AND include correct column-values

I am trying to get the minimum price per travel and know which travel-details correspond to that minimum price per travel.

For this I have tried all kind of variations with subqueries, joins etc. but since there is not 1 primary key I cannot figure it out.

What I'm trying to achieve is get the travel with the lowest price, and then included in the record the details of the travel with that lowest price.

SELECT travel_id, persons, days, MIN(`price`) AS `price`
FROM travel_details
WHERE 1
GROUP BY `travel_id`
HAVING MIN(price);


Simple version of my table-columns, columns are:
travel_id, persons, days, price

Those columns together form the primary key.

A travel can be booked for various persons, days and prices. It can also occur that there are multiple price-options for the same combination of travel_id, persons, and days.

E.g.,

100, 2, 4, **250**

100, 2, 4, **450**

100, 2, **5**, 450

101, 2, 4, 190

101, 2, 5, 185


Being travel_id 100 for 2 persons for 4 persons.

What I would like to achieve is return:
100, 250, and then with correct corresponding values:

100, 2, 4, 250

101, 2, 5, 185

Now my result just mixes all the other data. When I include those columns in the group by, it will not only group on travel_id anymore, but also e.g., on persons. Then it will return all combinations for a travel_id and person.

Any idea how to approach this? Every help is very much appreciated, since I'm breaking my head on this thing for days.

Answer

You can use IN() to do this:

SELECT * FROM travel_details t
WHERE (t.travel_id,t.price) IN(SELECT s.travel_id,min(s.price)
                               FROM travel_details s
                               GROUP BY s.travel_id)
GROUP BY t.travel_id; // this last group-by is to filter doubles when there are multiple records per travel with the lowest price.