SQL:
SELECT country, MAX(salary) AS max_salary
FROM employees
WHERE start_date > DATE '2021-01-01'
GROUP BY country
HAVING MAX(salary) > 100000
from employees
filter start_date > @2021-01-01
group country (
aggregate {max_salary = max salary}
)
filter max_salary > 100_000You'd think that you should be able to sort by the house ID, then by the sale price, then group by the house ID, and select the sale _date_ from the first row of each group.
Something like: `select s.house_id, first(s.date) from sale s order by s.house_id, s.price asc group by s.house_id`. But this is impossible, because there's no `first` function in SQL.
But nope, you need DB-specific extensions for that.
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
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.