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.
Row_number() over (partition by x order by) as rank
Then: where rank = 1.
Grouping is unordered, which is a clean definition.
Even now, the default behavior of SQLite is still "undefined" if a Select column doesn't appear in Group By. Well, to be precise it's a single "arbitrary chosen row" used to sort the results. https://www.sqlite.org/lang_select.html#resultset
I'm not bagging on SQLite here (I love it!), but I am bagging on SQL 'cuz EVERY implementation must make far too many of these sorts of Faustian bargains.
It's perfectly possible to do `SELECT * FROM blah LIMIT 1`, after all.
No, it's the morass of the interactions between GROUP BY/ORDER BY/HAVING. Like, why isn't there FIRST statement to select the first element in the group?