database - Postgres : Is it possible to show only one entry when duplicate occurs?

Question

我有一个包含重复条目的 postgres 数据库。我想显示 created_by 列和

id | name    | created_on
1  | 'Hello' | 3/29
2  | 'Hey'   | 4/3
3  | 'Hello' | 3/31
4  | 'Hey'   | 4/1

由于“你好”和“嘿”重复，我想形成一个查询来列出以下内容:

id | name    | created_on
2  | 'Hey'   | 4/3
3  | 'Hello' | 3/31

我如何形成该 SQL 查询？

实际上，我尝试将“DISTINCT ON”和“ORDER BY”放在一个查询中(使用 JOIN)，但出现以下错误:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

提前致谢

Answer 1

试试这个:

SELECT t1.*
FROM tablename AS t1
INNER JOIN
(
  SELECT name, MAX(created_on) AS MaxDate
  FROM tablename 
  GROUP BY name
) AS t2  ON t1.name       = t2.name
        AND t1.created_on = t2.maxdate;

SQL Fidle Demo