From the website for instance this is a nightmare to do in SQL:
from employees group role (sort join_date take 1) from employees
group role (
sort join_date
take 1
)
from employees | group role (sort join_date | take 1)
Take the 1st employee
by (earliest) join_date
for each role
from the set of employeesSELECT * FROM employees ORDER BY join_date LIMIT 1 BY role
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_dateThis 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.
https://clickhouse.com/docs/en/sql-reference/statements/sele...