With SQL, it is a trial and error. When your query passes your sniff tests, you sign looks good to me, and you ship it to the world. Only to silently break shortly afterwards without any warning.
In enterprise, I am convinced at this point that all of the complex ETL jobs are vending wrong outputs. Just nobody has the tools to diagnose and fix the problems.
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?
It's perfectly possible to do `SELECT * FROM blah LIMIT 1`, after all.
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.
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.
It is also the syntax because it does not match the actual model. Even the simple example `SELECT 1` shows a mismatch.
And this cascade. Because the syntax is wrong, people have big trouble with `JOINs` (that is disconnected from the idea of making `?-to-?` specifications), `group by` (that doesn't exist in SQL), aggregation logic (that is badly implemented as window functions), and bigly, the lack of composability (that has a weird band-aid called CTEs) and so on.
A mismatch between the domain and the code is always reflected in syntax.