问题描述
假设我有一张桌子-
A B C
1 3 5
1 3 7
1 3 9
2 4 3
2 4 6
2 4 1
在这里，A和B的相同组合有多个副本。对于每个组合，我想返回它的第一个条目。
，所以我要成为该表的结果-
here there are multiple copies for the same combination of A and B. for each combination I want back the first entry of it.so the result for this table i want to be-
A B C
1 3 5
2 4 3
如何在postgres sql中执行此操作？
How can I do this in postgres sql?
推荐答案
假设您可以在a，b和c上定义排序的 first，而您希望 DISTINCT ON 。
Assuming you can define "first" in terms of a sort on a, b, and c you want DISTINCT ON for this.
SELECT
DISTINCT ON ("A", "B")
"A", "B", "C"
FROM Table1
ORDER BY "A", "B", "C";
例如
请参见有关更多信息，请参见 DISTINCT ON 。
如果您在假设SQL表具有固有顺序方面犯了严重错误，则需要在继续操作之前先对表进行修复。您可以使用PostgreSQL ctid 伪列来指导创建与当前磁盘表顺序匹配的主键。应该安全：
If you have made the serious mistake of assuming SQL tables have an inherent order, you're going to need to fix your table before you proceed. You can use the PostgreSQL ctid pseudo-column to guide the creation of a primary key that matches the current on-disk table order. It should be safe to just:
ALTER TABLE mytable ADD COLUMN id SERIAL PRIMARY KEY;
因为PostgreSQL倾向于按表顺序写入密钥。不能保证，但是没有主键时也不能保证其他任何事情。然后，您可以：
as PostgreSQL will tend to write the key in table order. It's not guaranteed, but neither is anything else when there's no primary key. Then you can:
SELECT
DISTINCT ON ("A", "B")
"A", "B", "C"
FROM Table1
ORDER BY id;
（编辑：我不建议使用 ctid 进入应用程序的查询。它是解决特定问题的便捷工具，但它不是PostgreSQL中的真正公共API，也不是SQL标准的一部分，它不像 ROWID 在Oracle中，它会由于真空等原因而发生更改。PostgreSQL可以在将来的版本中随意对其进行破坏/更改/删除。）
( I don't recommend using ctid in queries baked into applications. It's a handy tool for solving specific problems, but it's not really public API in PostgreSQL, and it's not part of the SQL standard. It's not like ROWID in Oracle, it changes due to vacuum etc. PostgreSQL is free to break/change/remove it in future versions.)
这篇关于postgres查询以基于某些列的多个副本获取第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！