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.

hi when i tried to do this i got the result

i noticed that when the
+++ CreditCardSplitRateDetailKey +++
is the same for all records of the same member number
i get the correct result

Dont know how your data is !!!
It always helps a lot to see your data OR sample data

Anyhow here's my sample data

please click arrow to the left for sample data script .. with same CreditCardSplitRateDetailKey
drop table CreditCardSplitRateDetail
go 

create table CreditCardSplitRateDetail
(
CreditCardSplitRateDetailKey int not null,
MemberNumber varchar(50),
LoanNumber varchar(50),
SR_BalanceTypeIndex varchar(50),
SR_RateTypeIndex varchar(50),
SequenceNumber varchar(50),
SubloanRate float,
LastModifiedDate datetime 
)
go 

insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',9.1500,'2018-01-16 23:17:00.000'
insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',8.9000,'2017-12-18 23:32:00.000'
insert into CreditCardSplitRateDetail select 1111,'XXX0460','12X','0','0','0',9.9000,'2019-10-28 23:44:00.000'
go 

go 

select * from  CreditCardSplitRateDetail
go

please click arrow to the left for SQL of Moxioron
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)