我如何在SQL中找到重复的ROW_NUMBER值并获得最高值的数据集?

Question

我在SQL中按查询结果分组数据集。该数据有一个ROW_NUMBER值。这里，如果ROW_NUMBER值是一个顺序值，我希望获得最大的ROW_NUMBER值。我怎么能做到呢?我的SQL查询是这样的:

SELECT [_NAME]
,[LINE]
,[TIMESTAMP]
,[Equipment]
,[CL_Name]
,[Status]
,[ROW_NUMBER]
FROM [dbo].[Deflection]
where [TIMESTAMP] > DATEADD(day,-1,GETDATE()) and [Status] = 0
group by [CL_Name], [Equipment], [_NAME], [LINE], [TIMESTAMP], [Status], [ROW_NUMBER]
order by [ROW_NUMBER] desc, [TIMESTAMP] desc

查询的输出如下所示:

<表类="年代桌子">_NAME行时间戳设备CL_Name状态ROW_NUMBERtbody><01包22023-01-04 16:45:35.673包0288<01包/td>22023-01-04 16:50:35.673包0287<01包/td>22023-01-04 16:55:35.673包0286<02包/td>32023-01-04 23:20:36.063包B0209<03包/td>42023-01-04 23:20:36.063包C0209<03包/td>42023-01-04 23:25:36.067包C0208<03包/td>42023-01-04 23:30:36.073包C0207<03包/td>42023-01-04 23:35:36.077包C0206<01包/td>22023-01-04 23:45:36.067包0206<01包/td>22023-01-04 23:50:36.073包0205<01包/td>22023-01-04 23:55:36.077包0204

您可以为每个组按降序创建一个新的行号rn，然后为每个组选择#1。例如:

select x.*,
row_number() over(partition by cl_name order by [ROW_NUMBER] desc) as rn
from (
-- your query here
) x
where rn = 1

这里有一个缺口和孤岛问题，您可以使用两个row_number函数之间的差来解决它，如下所示:

Select Top 1 With Ties 
_NAME, LINE, TIMESTAMP, Equipment, CL_Name, Status, [ROW_NUMBER]
From
(
Select *,
ROW_NUMBER() Over (Order By TIMESTAMP) -
ROW_NUMBER() Over (Partition By _NAME Order By TIMESTAMP) As grp
From table_name
) T
Order By ROW_NUMBER() Over (Partition By _NAME, grp Order By [ROW_NUMBER] Desc)

等价于以下内容:

With create_groups AS
(
Select *,
ROW_NUMBER() Over (Order By TIMESTAMP) -
ROW_NUMBER() Over (Partition By _NAME Order By TIMESTAMP) As grp
From table_name
), 
max_row_num AS
(
Select *,
ROW_NUMBER() Over (Partition By _NAME, grp Order By [ROW_NUMBER] Desc) As rn
From create_groups
)
Select _NAME, LINE, TIMESTAMP, Equipment, CL_Name, Status, [ROW_NUMBER]
From max_row_num
Where rn = 1
Order By _NAME, TIMESTAMP

看到演示

我会选择:

SELECT CL_Name,
MAX(ROW_NUMBER)
FROM [dbo].[Deflection]
GROUP BY ROW_NUMBER