SQL 查询以查找最近的一组记录

SQL Query to find the most recent group of records(SQL 查询以查找最近的一组记录)
本文介绍了SQL 查询以查找最近的一组记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有共享 IdString 的记录历史记录(每次更新的多条记录都具有完全相同的日期时间).

I have a history of records (multiple records per update all with the exact same datetime) that share an IdString.

我想要一个查询来确定这些记录中的哪些是最近更新组的一部分.

I want a query to determine which of these records are part of the most recent update group.

此查询将向我显示一个具有最新更新日期的记录,但是对于每个分区,我需要具有该最大日期的所有记录..>

This query will show me one of the records having the most recent update date, but for each partition, I need all the records with that max date.

;with cte as(
select ROW_NUMBER() over (partition by IdString order by UpdateDate desc) as [rn], *
from MyTable
)
select  CASE WHEN (cte.rn = 1) THEN 0 ELSE 1 END [IsOld], *
from MyTable m
inner join cte on cte.RecordId= m.RecordId

有人可以帮我找出合适的查询吗?

Would someone please help me figure out an appropriate query?

示例

(IsOld is the desired calculated value)

IsOld RecordId  IdString  UpdateDate
1         1     ABC 2011-06-16 
1         2     ABC 2012-05-30 
1         3     ABC 2008-12-31 
0         4     ABC 2012-06-08 
1         5     ABC 2011-01-16 
0         6     ABC 2012-06-08 
1         7     ABC 2012-06-07 
1         8     XYZ 2001-01-16
1         9     XYZ 2013-01-30
0         10    XYZ 2001-01-31
1         11    XYZ 2013-06-01
1         12    XYZ 2001-05-04
0         13    XYZ 2013-01-30

推荐答案

试试这个 -

;WITH cte AS(
    SELECT RANK() OVER(PARTITION BY IdString ORDER BY UpdateDate DESC) AS [row_num], *
    FROM   MyTable
)
SELECT CASE WHEN m.[row_num] = 1 THEN 0 ELSE 1 END isOld, *
from cte m

这篇关于SQL 查询以查找最近的一组记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)