求SQL Server中等价PostgreSQL DISTINCT ON的正确查询语句

原PostgreSQL查询及效果 #

PostgreSQL里用DISTINCT ON能直接获取每个唯一email_addr对应的最新完整记录，语句如下：

select distinct on(email_addr) * 
from table1 
order by email_addr, created_date desc;

执行后得到70条记录，每条都是不同email_addr对应的最新数据。

尝试的SQL Server语句问题 #

SQL Server的DISTINCT是对所有选中列的组合去重，不是单独针对某一列，所以下面的语句会返回160条包含重复email_addr的记录：

select distinct(email_addr), id, first_name, last_name, created_date 
from table1 
order by email_addr, created_date desc;

正确的SQL Server实现语句 #

在SQL Server中，用ROW_NUMBER()窗口函数可以实现和PostgreSQL完全一致的效果，语句如下：

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY email_addr ORDER BY created_date DESC) AS rn
    FROM table1
)
SELECT *
FROM ranked_records
WHERE rn = 1;

逻辑说明 #

• PARTITION BY email_addr：将数据按email_addr分组
• ORDER BY created_date DESC：每组内按创建时间倒序排列，最新的记录排在首位
• ROW_NUMBER()为每组内的记录编号，最新记录的编号为1，最后筛选出编号为1的记录，就能得到每个email_addr对应的最新完整数据。

内容的提问来源于stack exchange，提问作者John Smith