anyavacy anyavacy - 6 months ago 15
SQL Question

Multiple unwanted records in Group by clause in Postgress

I have two table and I am joining them together then running a

group by
clause. The problem is that I keep getting unwanted data.

client table
----------
name
company_id
created_at


company table
-----------
name


Query:

SELECT company.name, clients.name, MIN (created_at) created_at
FROM company
INNER JOIN client
ON client.company_id = company.id
group by company.name, client.name


The query returns to me all the users, but what I want is only each one that was first created in each company. What should I change knowing that I need the clients names.

Answer

If you want the first one in each company, then use distinct on. This is a nice construct available only in Postgres:

SELECT DISTINCT ON (co.name), co.name, cl.name, cl.created_at
FROM company co INNER JOIN
     client cl
     ON cl.company_id = co.id
ORDER BY co.name, cl.created_at asc;