I have a query that can return the intended value but only 1 row. I need to have at least 26 rows of the values due based on the having clause.

SELECT town, floor_area_sqm, resale_price
FROM X.flat_prices as X
GROUP BY town
HAVING Min(floor_area_sqm) = (SELECT MIN(floor_area_sqm) FROM X.flat_prices HAVING MAX(resale_price));

By using the formula above I get this:

So the answer should show something like the following:

It should pull the lowest sqm for the town with the highest resale value. I got 26 towns and a database of over 200k.

I would like to replicate with MAX sqm using the same formula. Is join the way/only way to do it?

CodePudding user response：

Use a subquery to get the minimum sqm for each town. Join that with the table to get all the properties with that sqm. Then get the maximum resale value within each of these groups.

SELECT t1.town, t1.floor_area_sqm, MAX(t1.resale_value) AS resale_value
FROM flat_prices AS t1
JOIN (
    SELECT town, MIN(floor_area_sqm) AS floor_area_sqm
    FROM flat_prices
    GROUP BY town
) AS t2 ON t1.town = t2.town AND t1.floor_area_sqm = t2.floor_area_sqm
GROUP BY t1.town, t1.floor_area_sqm

DEMO

In MySQL 8.0 you can do it in one query with window functions, but I still haven't learned to use them.

CodePudding user response：

This one goes for the highest resale price , then choose the one with the lowest sqm if multiple choices exist.

select t1.town ,min(floor_area_sqm) mi_sqm,resale_value from flat_prices t1 
join
    (select town,max(resale_value) mx_value from flat_prices group by town) t2
on t1.town=t2.town and resale_value=mx_value
group by town
;