下面的查询结果记录如下..
如何只获取记录的最大行数行?我已经对20,000多条记录运行了这个查询,以识别重复项,并获得最近的日期及其处理的数量(RowNumber)。
select [FileName], ProcessDate,
ROW_NUMBER() over (partition by [FileName] order by [FileName] desc) RowNumber
from StagingTable
where filename = 'abc.txt'

发布于 2019-04-11 22:36:58
根据您的逻辑,似乎没有最大行数。或者行编号是否绑定到ProcessDate以外的另一个字段
据我所知,我将使用以下查询:
SELECT [FileName], MAX([ProcessDate]) AS [ProcessDate], COUNT(*) AS [Count]
FROM [StagingTable]
GROUP BY [FileName]发布于 2019-04-11 22:26:52
你在找过滤器吗?
select s.*
from (select [FileName], ProcessDate,
ROW_NUMBER() over (partition by [FileName] order by ProcessDate desc) as RowNumber
from StagingTable
where filename = 'abc.txt'
) s
where RowNumber = 1;请注意,ORDER BY已更改。
如果您还想要总计数,则将then作为附加列添加:
select s.*
from (select [FileName], ProcessDate,
count(*) over (partition by filename) as cnt,
ROW_NUMBER() over (partition by [FileName] order by ProcessDate desc) as RowNumber
from StagingTable
where filename = 'abc.txt'
) s
where RowNumber = 1;发布于 2019-04-11 22:52:07
您的查询有一个错误:在OrderBy中您编写了FileName,但我猜您使用的是日期。
您可以将ROW_NUMBER()列加倍:
WITH VersionSorting AS (
select
[FileName],
ProcessDate,
ROW_NUMBER() over (partition by [FileName] order by [ProcessDate] ASC) FileVersion,
ROW_NUMBER() over (partition by [FileName] order by [ProcessDate] DESC) FileVersionInv
from StagingTable
)
SELECT FileName, ProcessDate, FileVersion
FROM VersionSorting
WHERE FileVersionInv = 1
-- AND FileName = 'abc.txt' https://stackoverflow.com/questions/55634582
复制相似问题