5 ms·

Not sure what getting the first row has to do with any issues you have with GROUP BY/ORDER BY/HAVING? Each of those clauses serves a distinct purpose, and I'm n

by kmoser 2y ago

Not sure what getting the first row has to do with any issues you have with GROUP BY/ORDER BY/HAVING? Each of those clauses serves a distinct purpose, and I'm not sure I'd describe any interactions a "morass" since any connections between them are usually by design, and well documented. With the power of SQL (or any powerful and complex tool) comes great responsibility to understand how it works.

_dain_ 2y ago
WHERE and HAVING (and QUALIFY) are redundant. The PRQL language unifies them into a single "filter" construct: SQL: SELECT country, MAX(salary) AS max_salary FROM employees WHERE start_date > DATE '2021-01-01' GROUP BY country HAVING MAX(salary) > 100000 PRQL: from employees filter start_date > @2021-01-01 group country ( aggregate {max_salary = max salary} ) filter max_salary > 100_000
cyberax 2y ago
A very simple problem, you have a table of house sales. For each house (identified by an ID), you want to find the sale date that had the lowest price. 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.
- 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.

by kmoser 2y ago

_dain_ 2y ago

WHERE and HAVING (and QUALIFY) are redundant. The PRQL language unifies them into a single "filter" construct: SQL: SELECT country, MAX(salary) AS max_salary FROM employees WHERE start_date > DATE '2021-01-01' GROUP BY country HAVING MAX(salary) > 100000 PRQL: from employees filter start_date > @2021-01-01 group country ( aggregate {max_salary = max salary} ) filter max_salary > 100_000

cyberax 2y ago

A very simple problem, you have a table of house sales. For each house (identified by an ID), you want to find the sale date that had the lowest price. 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.

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.