Postgress的Group by子句中有多个不需要的记录 [英] Multiple unwanted records in Group by clause in Postgress

问题描述

我有两个表，我将它们连接在一起，然后运行 group by 子句。问题是我一直在获取不需要的数据。

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

查询：

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.

推荐答案

如果要在每个公司中都使用第一个，请使用与不同。

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;

这篇关于Postgress的Group by子句中有多个不需要的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！