处理查询返回的陷阱

Question

下面的查询返回几个重复的查询。

SELECT DISTINCT number, owner, date, provider FROM
data.provider

产出-

"05"    "Government - State/ County"    "1966-07-01"    "H"
"05"    "Proprietary"                   "1966-07-01"    "H"
"67"    "Proprietary"                   "2002-12-16"    "H"

我希望查询只返回重复number列记录的一条记录。所以，我想要这个输出而不是-

"05"    "Government - State/ County"    "1966-07-01"    "H"
"67"    "Proprietary"                   "2002-12-16"    "H"    

我希望它能根据记录年份区分两个"05“记录。最近的一年获胜。年份是provider表的一部分。

Answer 1

您可以使用row_number()。以获得每个提供者的唯一编号。

select * from 
    (select row_number() over (partition by owner, number order by date) rn
        , number, owner, date, provider from data.provider) t1
where t1.rn = 1

Answer 2

在Postgres，我推荐DISTINCT ON

SELECT DISTINCT ON (number) number, owner, date, provider
FROM data.provider
ORDER BY number, date DESC;