如何在PostgreSQL中获取同一列的多个值的最新记录？

Question

我有一个如下结构的数据库

 url  update_time                 dns_time
 -------------------------------
 url1  2013-04-05 08:03:23       0.897
 url2  2013-09-03 08:03:45       0.765
 url1  2013-08-23 09:23:34       2.457
 url3  2013-08-34 09:45:47       1.456
 //and so on

现在我想仅检索每个url的最新记录。如何使用PostgreSQL select查询实现此目标。

我尝试过使用

 select url,
        update_time,
        dns_time 
 from dns_lookup_table 
 where url in('url1','url2','url3') 
 order by desc limit 1

但是它给我返回了最后一条记录的url3最新值。我尝试使用desc limit 3获取所有3个URL的最新值。我想要检索url1，url2和url3的最新记录。仅最新记录。表dns_lookup_table动态地生成记录。有时如果URL记录不可用，则无法插入。因此失去了顺序。所以我认为使用desc limit不可能实现。

- user2515189

---

update_time 的类型是什么？ - cruxi

“So order is missing” 的意思是什么？ - user330315

如果我使用desc limit 5，URL的顺序将会丢失。如果数据库中没有插入具有该URL的记录。 - user2515189

update_time 在 postgresql 中是 timestamp 类型。 - user2515189

Answer 1

您可以使用窗口函数来获取每个URL的最新行：

select *
from (
   select url, 
          update_time, 
          dns_time, 
          row_number() over (partition by url order by update_time desc) as rnk
   from dns_lookup_table
) as t
where rnk = 1

SQLFiddle示例：http://sqlfiddle.com/#!12/fbd38/1

---

编辑

您还可以使用类似以下方式的内容：

select *
from dns_lookup_table lt
  join (
     select url, 
            max(update_time) as latest_time
     from dns_lookup_table
     group by url
  ) as mt on mt.latest_time = lt.update_time 
         and mt.url = lt.url;

- user330315

---

我正在使用PostgreSQL 9.0版本。 - user2515189

@user2515189：这样窗口函数就能正常工作了。你可能复制了错误的代码。你能否上传一个样例到http://sqlfiddle.com并展示问题呢？ - user330315

Answer 2

SELECT *
 FROM dns_lookup_table lut
 WHERE NOT EXISTS (
   SELECT *
   FROM dns_lookup_table nx
   WHERE nx.url = lut.url
     AND nx.update_time > lut.update_time
   );  

- joop