如何获取PostgreSQL中同一列的多个值的最新记录? [英] How to get the latest records of the multiple values of same column in PostgreSQL?
问题描述
我有一个具有以下结构的数据库
I have a database with following structure
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的最新记录。
Now I want to retrieve only latest records of each url. How to achieve this using PostgreSQL select query.
我尝试使用
select url,
update_time,
dns_time
from dns_lookup_table
where url in('url1','url2','url3')
order by desc limit 1
但是它给了我 url3 最近的值,这是最近的记录。我尝试使用降序限制3 来获取所有3个网址的最新值。我想检索 url1 , url2 , url3 的最新记录。仅最新记录。表 dns_lookup_table
包含动态记录。有时,如果不可用,则无法插入url记录。因此缺少订单。因此,我认为使用 desc限制
是不可能的。
But it is giving me the url3 latest value that is last record. I tried with desc limit 3 for getting latest values of all 3 urls. I want to retrieve the latest records of url1, url2, url3. Only latest records. And the table dns_lookup_table
has records that comes into it dynamically. Sometimes the url record can not be inserted if not available. So order is missing. So I think it is not possible with desc limit
.
推荐答案
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
);
这篇关于如何获取PostgreSQL中同一列的多个值的最新记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!