The many faces of DISTINCT in Postgres (2017)
> A classic job interview question is finding the employee with the highest salary in each department.<p>Here’s a cheat code, in case you need to write a database query and don’t remember all the fancy join tricks: Problems like this often have a very straightforward solution with subselects. In this case, the main select gets the departments, and a subselect (with limit 1) fetches the top employee for each department.<p>That’s a very natural, compositional way of thinking. Granted, it’s not the most optimized way to do it, but more often then not, the resulting query plan is perfectly fine.