CODEWITHSUNDEEP
sqlsql-servert-sql
MikeAustin
MikeAustin

Reputation: 31

Return the most recent record according to a snapshot date

I have a table with 4 columns (snapshotDate, systemID, companyID, State) the table can have different snapshots for each system for the same companyID in the same state.

I want to write a query to return the most recent record for each record in the month for each system with in the same companyID in and same state.

For example:

snapshotDate    systemID    companyID   State
12/31/2017           A        2         FL
12/30/2017           A        2         FL
12/29/2017           A        2         FL
03/25/2018           B        5         WA
03/20/2018           B        5         WA

In this case I want the result to be as follows:

snapshotDate    systemID    companyID   State
12/31/2017           A        2         FL 
03/25/2018           B        5         WA

Thanks Mike

Upvotes: 1

Views: 5635

Answers (3)

Anand Singh
Anand Singh

Reputation: 210

You can use this query 

select max(snapshotDate) snapshotDate ,systemID, companyID, State 
from tablename 
group by systemID, companyID, State;

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use window function with top(1) with ties

select top(1) with ties systemID, *
from table t
order by row_number() over (partition by systemID order by snapshotDate desc)

You could also use subquery instead 

select * from table t
where snapshotDate = (select max(snapshotDate) from table where systemID = t.systemID)

But, as per your sample data you could do that via group by clause

select max(snapshotDate) snapshotDate, systemID, companyID, State
from table t
group by systemID, companyID, State

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

You can achieve this using WINDOW FUNCTION Row_Number().

TRY THIS:

SELECT snapshotDate,systemID,companyID,State 
FROM(
    SELECT snapshotDate,systemID,companyID,State
        ,ROW_NUMBER() OVER(PARTITION BY systemID ORDER BY snapshotDate DESC)RN  
    FROM Your_Table
    )D
WHERE D.RN = 1

Upvotes: 0

Related Questions