是否可以在没有子查询的情况下检索每个组的顶部ROW_NUMBER()

Question

本着简洁美观的编码精神，我想知道是否有人能够创建一个执行如下的查询，但没有子查询。

查询示例：

基本上，子查询是通过首先对每个患者的每一行进行排序和标记来检索每个用户的最新日期；1〃；是每个患者的最新日期。外部查询然后识别并返回每个"0"；1〃；其是与每个用户相关联的最大日期。

SELECT user_name
,date
FROM(
SELECT user_name
,date
,row_number() OVER(PARTITION BY user_name ORDER BY date DESC) as row_num
FROM yourtable
) AS t
WHERE t.row_num = 1

数据示例预执行：

|user_name|date |
|Some1    |10/22|
|Some1    |10/27|
|Some1    |10/30|
|Some2    |10/30|
|Some2    |11/15|
|Some2    |11/18|

执行后的数据示例：

|user_name|date |
|Some1    |10/30|
|Some2    |11/18|

准备让我的头脑被你们所有人震撼

Answer 1

例如，您可以使用group by:

select user_name, max(date)
from t
group by user_name;

Answer 2

您可以使用WITH TIES选项

Select top 1 with ties *
From  yourtable
Order By row_number() OVER(PARTITION BY user_name ORDER BY date DESC) 

Answer 3

您可以使用SELECT DISTINCT、LAST_VALUE和MAX() OVER的组合(指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING(。像这样的

数据

drop table if exists #tTable;
go
create table #tTable(
user_name     varchar(100) not null,
dt            date not null);

Insert into #tTable values
('Some1', '20201022'),
('Some1', '20201027'),
('Some1', '20201030'),
('Some2', '20201030'),
('Some2', '20201115'),
('Some2', '20201118');

查询

select distinct last_value(user_name) over (partition by user_name order by dt) as user_name,
max(dt) over (partition by user_name order by dt 
rows between unbounded preceding
and unbounded following) as [date]
from #tTable;

输出

user_name   date
Some1       2020-10-30
Some2       2020-11-18