2 ms·
I don't use PRQL but I absolutely get the appeal but specifically on the readability part, some things that are easy in PRQL are just awful in SQL. From the we
by the_mitsuhiko 3y ago
I don't use PRQL but I absolutely get the appeal but specifically on the readability part, some things that are easy in PRQL are just awful in SQL.
From the website for instance this is a nightmare to do in SQL:
from employees group role (sort join_date take 1)
- zX41ZdbW 3y agoWhat does it mean? Is it the same as "LIMIT BY" in ClickHouse? https://clickhouse.com/docs/en/sql-reference/statements/select/limit-by https://clickhouse.com/docs/en/sql-reference/statements/sele...
- the_mitsuhiko 3y agoIt picks the first result grouped by role and sorted by join date. I believe this can be expressed with limit by in ClickHouse.
- snthpy 3y agoUnfortunately the linebreaks were lost and, as shown that isn't a valid PRQL query. It would have to be either from employees group role ( sort join_date take 1 ) or from employees | group role (sort join_date | take 1) In English: Take the 1st employee by (earliest) join_date for each role from the set of employees
- zX41ZdbW 3y agoClickHouse: SELECT * FROM employees ORDER BY join_date LIMIT 1 BY role
- virtualwhys 3y agoWhat is the generated SQL of that expression? This is indeed a sticky problem, one that usually requires a subselect or other workaround to address the non-determinism of group by + order by; i.e. one cannot simply "select * from employees group by role order by join_date limit 1" and be guaranteed to get the expected ordering.
- housecarpenter 3y agoIsn't this a fairly simple way of doing this? That said, it is a bit non-obvious if you haven't seen it before. select earliest_joiner.* from employees as earliest left join employees as earlier on earlier.role = earliest.role and earlier.join_date < earliest.join_date where earlier.id is null order by earliest.join_date