3 ms·
What do you want to see if there's more than one sale date with the lowest price? In any case, you can do this with a subquery and join it with the main table.
by kmoser 2y ago
What do you want to see if there's more than one sale date with the lowest price?
In any case, you can do this with a subquery and join it with the main table. I don't think there's anything non-standard about it:
SELECT hs.*
FROM house_sales hs
JOIN (
SELECT house_id, MIN(price) AS min_price
FROM house_sales
GROUP BY house_id
) min_prices
ON hs.house_id = min_prices.house_id AND hs.price = min_prices.min_price
I'm sure you can do it with window functions as well.
- cyberax 2y ago> What do you want to see if there's more than one sale date with the lowest price? Either one is fine. PostgreSQL has an extension (DISTINCT ON) that allows to do what I want without subqueries. But standard SQL is simply deficient in this regard, it should be straightforward but it's not.