如何使用 Postgresql 查找组中最旧的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6363205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find the oldest record in a group using Postgresql?
提问by Huuuze
Consider the following data set in a "book" table (group_id, title, check_out_date):
考虑“book”表中的以下数据集（group_id、title、check_out_date）：
> 1 - "Moby Dick" - 2010-01-01
> 1 - "The Jungle Book" - 2011-05-05
> 1 - "Grapes of Wrath" - 1999-01-12
> 2 - "Huckleberry Finn" - 2000-01-05
> 2 - "Tom Sawyer" - 2011-06-12
I need to write a query that will return the record with the oldest "check_out_date" value from each group (Group 1 and Group 2). This should be fairly easy -- I just don't know how to do it.
我需要编写一个查询，该查询将返回每个组（第 1 组和第 2 组）中最旧的“check_out_date”值的记录。这应该相当容易——我只是不知道该怎么做。
回答by NullRef
I think you need something like this.
我想你需要这样的东西。
select group_id, title, check_out_date from book b1
where
check_out_date =
(select MIN(check_out_date)
from book b2 where b2.group_id = b1.group_id)
回答by Andrew Lazarus
Now that postgres supports windowing functions.
现在 postgres 支持窗口函数。
SELECT group_id, title, checkout_date) FROM
(SELECT group_id,
title,
checkout_date,
rank() OVER (PARTITION BY group_id ORDER BY checkout_date) AS rk
) AS subq
WHERE rk=1;
You probably want an index on (group_id, checkout_date), perhaps vice versa. I haven't banged on windowing enough to know what the planner tries to do.
您可能想要一个关于 的索引(group_id, checkout_date)，反之亦然。我还没有对窗口进行足够的了解以了解计划者试图做什么。
