Question 89391 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

4

Answers


  1. 0

    I’d use the row_number window function to assign a numbering within each company, and then use that to query the first person:

    SELECT c_id, c_name, p_id, p_name
FROM   (SELECT    c.id AS c_id,
                  c.name AS c_name,
                  p.id AS p_id,
                  p.name AS p_name,
                  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id ASC) AS rn
        FROM      company c
        LEFT JOIN person p ON p.company_id = c.id) t
WHERE  rn = 1
    Login or Signup to reply.
  2. 0

    This is how to get the correct name :

    You will have to join your result with the person table by person.id = s.employee_person_id

    select s.company_id, s.company_name, s.employee_person_id, p.name as employee_person_name
from person p
inner join (
    SELECT
      company.id as company_id,
      company.name as company_name,
      MIN(person.id) AS employee_person_id
    FROM company
    LEFT OUTER JOIN person ON (person.company_id = company.id)
    GROUP BY company.id
) as s on s.employee_person_id = p.id
    Login or Signup to reply.
  3. 0

    In Postgres, I would recommend distinct on:

    SELECT distinct on (c.id)
  c.id,
  c.name,
  p.id AS employee_person_id,
  p.name AS employee_person_name
FROM company c
LEFT OUTER JOIN person p ON p.company_id = c.id
ORDER BY c.id, p.id

    For each company, this brings the person with the smallest id; you control which person is picked with the ORDER BY clause (if you wanted the person with the greatest id, you would use ORDER BY c.id, p.id DESC).

    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search