How to get latest record from a SQL Server table b

问题:

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

Result:

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

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.

Thanks in advance

回答1:

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

回答2:

SELECT top 1 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')  order by [TimeStamp] DESC