如何根据时间戳从 SQL Server 表中获取最新记录 [英] How to get latest record from a SQL Server table based on timestamp

问题描述

我正在尝试根据时间戳从表中获取最新记录.他是我写的查询:

I am trying to get the latest record from a table based on the time stamp. He is query I wrote:

SELECT DISTINCT
    [Year], 
    [Type],
    [Category],
    [AnnualCost],
    MAX([TimeStamp]) OVER (PARTITION BY [Year], [Type], [Category], [AnnualCost]) AS MaxTimeStamp
FROM 
    [PromOneSite].[Budgeting].[MISBasePrice]
WHERE
    Year = 2016
    AND category IN ('Leasing Office Desktop')
    AND TimeStamp IS NOT NULL

结果:

Year    Type                           Category                 AnnualCost   MaxTimeStamp
----------------------------------------------------------------------------
2016    Equipment Hardware Location    Leasing Office Desktop       750.00     2015-10-14 17:54:09.510
2016    Equipment Hardware Location    Leasing Office Desktop       850.00     2015-10-14 17:54:20.630

我得到了这两条不同数量和不同时间戳的记录.我知道这是因为我在查询中加入了 distinct，它也给我带来了不同的 Annualcost.但是没有不同的我会得到大约 30 多个重复记录.

I get these two records with different amounts and different timestamps. I know that it is because I put distinct in the query it brings me distinct Annualcost as well. But without the distinct I get about 30+ duplicate records.

在这种情况下，如何只获取一条具有最新时间戳的记录.

How can just get only one record with the latest timestamp in this scenario.

提前致谢

推荐答案

select * from 
(  SELECT [Year]
         ,[Type]
         ,[Category]
         ,[AnnualCost]
         ,[TimeStamp] as MaxTimeStamp
         ,row_number() over (partition by [Year], [Type], [Category] order by [TimeStamp] desc ) as rn 
     FROM [PromOneSite].[Budgeting].[MISBasePrice]
    where Year = 2016
      and category IN ('Leasing Office Desktop')
      and TimeStamp IS Not Null 
) tt
where tt.rn = 1

这篇关于如何根据时间戳从 SQL Server 表中获取最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！