SQLTeam.com | Weblogs | Forums

Get the most recent record using Max as a condition

Hello.

I thought I figured it out, but the results are not reflecting what I am asking for.

Here is the code:
Select
ccs.MemberNumber,
ccs.LoanNumber,
ccs.SubloanRate,
ccs.LastModifiedDate

From

CreditCardSplitRateDetail ccs

Where
ccs.MemberNumber='XXX0460'
AND ccs.LoanNumber='12X'
AND ccs.SR_BalanceTypeIndex='0' AND ccs.SR_RateTypeIndex='0' AND ccs.SequenceNumber='0'
AND ccs.LastModifiedDate = (Select Max(ccs2.LastModifiedDate) From CreditCardSplitRateDetail ccs2 Where ccs2.CreditCardSplitRateDetailKey=ccs.CreditCardSplitRateDetailKey)

An the results:
image

I just want the most recent date ... but I am getting all three still.

Thanks for your help.

Select
    MemberNumber,
    LoanNumber,
    SubloanRate,
    LastModifiedDate
From (
    Select
    ccs.MemberNumber,
    ccs.LoanNumber,
    ccs.SubloanRate,
    ccs.LastModifiedDate,
    Row_Number() Over(Partition by ccs.MemberNumber Order by ccs.LastModifiedDate Desc) As RowNum

    From

    CreditCardSplitRateDetail ccs

    Where
    ccs.MemberNumber='XXX0460'
    AND ccs.LoanNumber='12X'
    AND ccs.SR_BalanceTypeIndex='0' 
    AND ccs.SR_RateTypeIndex='0' 
    AND ccs.SequenceNumber='0'
) as derived
Where RowNum = 1
Order by MemberNumber

Thank you. That worked ... just seems like a lot of work to get the outcome!

I appreciate your response/solution.

You're welcome. That's the cleanest way I know to do it, that is, with the least I/O and code.

You could also use this little trick:

 Select Top 1 With Ties 
        ccs.MemberNumber
      , ccs.LoanNumber
      , ccs.SubloanRate
      , ccs.LastModifiedDate
   From CreditCardSplitRateDetail ccs
  Where ccs.MemberNumber = 'XXX0460'
    And ccs.LoanNumber = '12X'
    And ccs.SR_BalanceTypeIndex = '0'
    And ccs.SR_RateTypeIndex = '0'
    And ccs.SequenceNumber = '0'
  Order By
        row_number() Over(Partition by ccs.MemberNumber Order by ccs.LastModifiedDate Desc);
1 Like

I guess I should have stated that I used the "derived" table style to allow for multiple MemberNumbers to be listed.

The single "Top (1)" without a derived table with work only with one and only one MemberNumber. If you wanted to list the TOP 1 for, say, all MemberNumbers, that code won't have to be rewritten (and I suspect jeff would rewrite it to be a derived table using ROW_NUMBER() in the inner query).

The 'Top 1 With Ties' will handle multiple member numbers - since the partition is by member number. The Order By results in a value of 1 for the latest date per member number - and the Ties includes each member number.

As with anything - there are situations where one or the other will perform better and in those cases I will utilize the other method.