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_000

You'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.