0

I have a single database table 'Accomodation' that lists information of hotel/B&B suites with the city they are located in, the price and an ID for each suite that functions as the primary key.

It looks similar to this:

id     | city       | Price
ams001 | Amsterdam  | 160
ams011 | Amsterdam  | 120
par004 | Paris      | 90
par006 | Paris      | 120
rom005 | Rome       | 130
rom015 | Rome       | 130

I want to list all information of the cheapest accomodation for each city, however if two records both share the same lowest price I want to display both of these.

The result should look something like this:

ams011 | Amsterdam  | 120
par004 | Paris      | 90
rom005 | Rome       | 130
rom015 | Rome       | 130

I have tried using 

SELECT * FROM accomodation
WHERE price IN (SELECT MIN(price) FROM accomodation GROUP BY city);

However this will produce a table like this 

ams011 | Amsterdam  | 120
par004 | Paris      | 90
par006 | Paris      | 120
rom005 | Rome       | 130
rom015 | Rome       | 130

Since the 120 price is the cheapest for Amsterdam it will show up at Paris too. If I add a group by statement at the end, outside of the subquery like this:

SELECT * FROM accomodation
WHERE price IN (SELECT MIN(price) FROM accomodation GROUP BY city)
GROUP BY city;

It will fail to display lowest values that are identical and I'm left with a table like this:

ams011 | Amsterdam  | 120
par004 | Paris      | 90
rom015 | Rome       | 130
Tim B
  • 3
  • 3

1 Answers1

0

First GROUP BY city to get the min price for each city and then join to the table:

SELECT a.*
FROM accomodation a INNER JOIN (
  SELECT city, MIN(price) minprice
  FROM accomodation
  GROUP BY city
) g ON g.city = a.city AND g.minprice = a.price
forpas
  • 160,666
  • 10
  • 38
  • 76