Pulling most recent value from a range of values

Question

I'm trying to get only the most recently added value from the field CDT.Value. As it stands, Value = CDT.Value doesn't work because it tries to return multiple values. MAX(CDT.Value) returns the highest number ever added for each combination of fields. COUNT(CDT.Value) returns the total number of different numbers ever input for the value.

I want to get the most recently inputted value. RPS.EffectiveStartDate indicates the most recently inputted date/time. Basically: CDT.Value at MAX(RPS.EffectiveStartDate).

There are a good number of similar questions answered, most of them implement a SELECT in a subquery, but I wasn't able to get anything useful in my attempts to implement them.

I'm thinking the subqueried SELECT needs to come after INNER JOIN vw_CurveDetail statement but not totally clear on how it should be done.

How to find the record in a table that contains the maximum value?

SQL select max(date) and corresponding value

how do I query sql for a latest record date for each user

Here's a snippet from the query I'm dealing with: it's pretty lengthy so I didn't want to include the whole thing

SELECT      RPS.MarketIdentifier
,           RPS.Jurisdiction
,           RPS.CurveType
,           RPS.RECType
,           MAX(RPS.EffectiveStartDate) EffectiveStartDate
,           CDT.ApplicationStartDate
,           Value = MAX(CDT.Value)
,           Year = CDT.Year
FROM        #RPSCurve                   RPS
INNER JOIN  vw_CurveDetail CDT ON   CDT.CurveIdentifier = RPS.CurveIdentifier
                                        AND CDT.EffectiveStartDate = RPS.EffectiveStartDate
INNER JOIN  #PlanningYearStartMonth     PYS ON  PYS.Jurisdiction = RPS.Jurisdiction

score 0 · Answer 1 · answered Nov 09 '15 at 19:38

0

You can add this at the end of query

order by RPS.EffectiveStartDate desc

answered Nov 09 '15 at 19:38

Coder221

1,403
2
19
35

score 0 · Answer 2 · answered Nov 09 '15 at 21:10

In order to return just one row of data relating to the most recent EffectiveStartDate you can add the TOP 1 statement with an ORDER BY, as below:

SELECT TOP 1      
            RPS.MarketIdentifier
,           RPS.Jurisdiction
,           RPS.CurveType
,           RPS.RECType
,           RPS.EffectiveStartDate
,           CDT.ApplicationStartDate
,           CDT.Value
,           CDT.Year
FROM        #RPSCurve RPS
INNER JOIN  vw_CurveDetail CDT ON CDT.CurveIdentifier = RPS.CurveIdentifier
                               AND CDT.EffectiveStartDate = RPS.EffectiveStartDate
INNER JOIN  #PlanningYearStartMonth PYS ON PYS.Jurisdiction = RPS.Jurisdiction
ORDER BY RPS.EffectiveStartDate DESC

Pulling most recent value from a range of values

2 Answers2