每个 id 的最近(最大)日期 [英] most recent (max) date for every id

问题描述

a = id  
b = date  
c = NewestDate

对于每个 a，我有 b c

For every a, I have b c

如何获取每组 ID 的最新日期(我的a"列)?它们并非都具有相同的结束日期.所以我确实需要一个最大值.

How can I get the newest date for every set of id's (MY 'a' column)? they don't all have the same end date. So I do need a max for all of them.

数据在myTable中，到目前为止，我尝试过:

The data is in myTable, so far i tried:

select * 
into #myTable 
from myTable

select 
t.a
,t.b
,t.c 
,(select max(b) from myTable) as c
from myTable t 
left join #myTable t1
on t.a = t1.a and t.b = t1.b
order by a, b 

上面代码的问题是在'c'中放置了它们的最大日期，这不是我真正想要的.

The problem with the above code is that in 'c' it is placed the max date of them all, which is not what I actually want.

问题现在已通过 Dmitry Poliakov 给出的答案解决(谢谢).使用:

the problem is now solved with the answer given by Dmitry Poliakov (thanks). Used:

SELECT a,
    b,
    max(b) OVER (PARTITION BY a) AS c
FROM myTable
ORDER BY a,b

推荐答案

可以为每组id选择最大日期

you can select maximum date for each group of ids as

SELECT a,
    b,
    max(b) OVER (PARTITION BY a) AS c
FROM myTable
ORDER BY a,b

问题的第二(编辑)部分的可能解决方案之一是

one of possible solutions for the second(edited) part of question is

WITH cte AS (
    SELECT a,
        b,
        max(b) OVER (PARTITION BY a) AS c,
        d
    FROM myTable
)
SELECT t1.a,
    t1.b,
    t1.c,
    t1.d,
    t2.d AS e
FROM cte t1
JOIN cte t2 ON t1.a=t2.a AND t1.c=t2.b

这篇关于每个 id 的最近(最大)日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！