T-SQL中如何筛选最大row_number值，获取Code最新描述？

需要获取每个code_id对应的最后一条code_description，尝试使用over partition by...order by code_description desc未达到预期效果，且WHERE子句中不能使用聚合函数。数据从Excel导入。

期望结果：

测试代码：

Create table #test 
(
    code_id int,
    Code_description varchar(100)
)

insert into #test values (10000, 'Maintain healthy bone')
insert into #test values (10000, 'Maintain healthy teeth')
insert into #test values (10000, 'Increases Immune')
insert into #test values (10000, 'Maintain healthy bone,teeth and increase immune')

With cte as
(
    select 
        code_id, Code_description,
        row_number() over (partition by code_ID order by code_id) rn
    from 
        #test
)
select * 
from cte 
where rn = max(rn)
group by code_id, Code_description

原代码问题 #

1. 窗口函数中order by code_id无效：同一code_id下所有行的code_id值相同，row_number()无法按预期生成有序序号，结果顺序不确定。
2. WHERE rn = max(rn)语法错误：聚合函数max()不能直接用于WHERE子句，且后续group by的分组逻辑错误，无法筛选出目标行。

正确实现方式 #

场景1：获取内容最完整（最长）的记录

如果你的“最后一条”指的是合并后内容最完整的记录，可以通过按Code_description长度倒序排序来获取：

Create table #test 
(
    code_id int,
    Code_description varchar(100)
)

insert into #test values (10000, 'Maintain healthy bone')
insert into #test values (10000, 'Maintain healthy teeth')
insert into #test values (10000, 'Increases Immune')
insert into #test values (10000, 'Maintain healthy bone,teeth and increase immune')

With cte as
(
    select 
        code_id, Code_description,
        row_number() over (partition by code_ID order by len(Code_description) desc) rn
    from 
        #test
)
select code_id, Code_description
from cte 
where rn = 1

场景2：获取Excel导入时的最后一行（插入顺序）

由于原表无顺序标识，建议先添加自增列记录插入顺序，再筛选：

Create table #test 
(
    id int identity(1,1) primary key, -- 添加自增列记录插入顺序
    code_id int,
    Code_description varchar(100)
)

insert into #test (code_id, Code_description) values (10000, 'Maintain healthy bone')
insert into #test (code_id, Code_description) values (10000, 'Maintain healthy teeth')
insert into #test (code_id, Code_description) values (10000, 'Increases Immune')
insert into #test (code_id, Code_description) values (10000, 'Maintain healthy bone,teeth and increase immune')

With cte as
(
    select 
        code_id, Code_description,
        row_number() over (partition by code_ID order by id desc) rn
    from 
        #test
)
select code_id, Code_description
from cte 
where rn = 1

另一种实现：使用MAX窗口函数

无需额外分组，直接通过窗口函数获取每个分组的最大序号，再筛选匹配行：

With cte as
(
    select 
        code_id, Code_description,
        row_number() over (partition by code_ID order by len(Code_description) desc) rn,
        count(*) over (partition by code_ID) max_rn -- 获取当前分组的最大序号值
    from 
        #test
)
select code_id, Code_description
from cte 
where rn = max_rn

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