SQL: Getting the “first” item of a joined table

Question

Let’s say I want to show a list of companies, and also a first/random employee of that company.

I could do something like:

SELECT
  company.id,
  company.name,
  MIN(person.id) AS employee_person_id,
  MIN(person.name) AS employee_person_name
FROM company
LEFT OUTER JOIN person ON (person.company_id = company.id)
GROUP BY company.id;

But I think with my code above, MIN(person.id) and MIN(person.name) could give info about two different people, right?

Is there a better way of retrieving just a “first” (or random) employee and showing that person’s ID and name?

Answer 1

This is correct:

MIN(person.id) and MIN(person.name) could give info about two different people, right?

You can see that happening in the demo linked below.

GMB's distinct on is typically the most recommended, obvious choice, but it requires ordering, same as Mureinik's. Meanwhile, you can let each company just get any single person, without having to order them first: demo

SELECT company.id, company.name, person.id, person.name
FROM company LEFT JOIN LATERAL
(SELECT id,name FROM person WHERE company_id=company.id LIMIT 1) person
             ON true;

Even simpler if you don't have companies with no persons, or if you ignore such companies:

SELECT company.id, company.name, person.id,  person.name
FROM company, 
LATERAL (SELECT id,name FROM person WHERE company_id=company.id LIMIT 1) person;

It's "retrieving just a “first” (or random) employee" the convenient way: it takes whatever it happens to find first, without having to find and order all possible matches before picking one. Each company just fetches any one of their people, which seemed to be the idea.

Thanks to not doing the extra work, it's faster (16'000x on 300'000 row sample) and it scales in proportion to only the company, pretty much disregarding the person table's size and growth.