Multiple joins chose latest record by date-CodePudding

I am trying to link four tables (3 of them key to this question). I need to pull the latest payment type used from T16. T16 links to T17 via headerid, which links to A10 via pledgeid.

I have tried this a bunch of different ways. The code below is giving me the latest date for each payment type, but what I really want is just the last payment type.

SELECT DISTINCT
    A10.RecordId
    ,A10.AccountNumber
    ,A01.FamilyId
    ,a01.FamilyMemberType
    ,A10.PledgeCode  --Child Number
    ,A10.OriginalPledgeId
    ,A10.PledgeId
    ,A01.FirstName
    ,A01.LastName
    ,A10.PledgeStatus
    ,A10.AmountPerGift
    ,A10.PledgeFrequency
    ,t16.PaymentType
FROM 
    A10_AccountPledges A10
LEFT JOIN
    A01_AccountMaster A01 ON a01.AccountNumber = a10.AccountNumber
LEFT JOIN
    T17_RecurringDonations T17 ON T17.PledgeId = A10.PledgeId
LEFT JOIN
    T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
INNER JOIN
    (SELECT 
         T17.pledgeID
         ,MAX(T16.LastUsedDate) as lastdate
     FROM 
         T17_RecurringDonations T17
     LEFT JOIN
         T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
     GROUP BY
         T17.pledgeID) pm ON pm.PledgeId = A10.PledgeId --and pm.lastdate = T16.LastUsedDate
WHERE 
    A01.[Status] = 'A'
    AND a10.PledgeId = 398353 --test case

CodePudding user response：

You can do this like below:

select top 1 *
from (
    YOUR QUERY HERE
)
order by lastdate desc;

Notes:

YOUR QUERY HERE is a placeholder which will hold your whole query as a subquery
you need to add the selection of lastdate to your subselect in order to make sure you can order by lastdate desc

EDIT

As @spaindc explained, this idea was applied, resulting in

   SELECT distinct 
       A10.AccountNumber
      ,A01.FamilyId
      ,a01.FamilyMemberType
      ,A10.PledgeCode  --Child Number
      ,A10.OriginalPledgeId
      ,A10.PledgeId
      ,A01.FirstName
      ,A01.LastName
      ,A10.PledgeStatus
      ,A10.PledgeFrequency
      ,(SELECT top 1 T16.PaymentType
          FROM T16_RecurringTransactionHeaders T16, T17_RecurringDonations T17
          where T16.HeaderId = T17.HeaderId
          and T17.PledgeId = A10.PledgeId
          order by t16.LastUsedDate desc
        ) as PaymentType
      FROM A10_AccountPledges A10
      left join A01_AccountMaster A01 on a01.AccountNumber = a10.AccountNumber
      where A01.[Status] = 'A'

CodePudding user response：

Thanks to Lajos for correct answer. Here is the final code for reference.

   SELECT distinct 
       A10.AccountNumber
      ,A01.FamilyId
      ,a01.FamilyMemberType
      ,A10.PledgeCode  --Child Number
      ,A10.OriginalPledgeId
      ,A10.PledgeId
      ,A01.FirstName
      ,A01.LastName
      ,A10.PledgeStatus
      ,A10.PledgeFrequency
      ,(SELECT top 1 T16.PaymentType
          FROM T16_RecurringTransactionHeaders T16, T17_RecurringDonations T17
          where T16.HeaderId = T17.HeaderId
          and T17.PledgeId = A10.PledgeId
          order by t16.LastUsedDate desc
        ) as PaymentType
      FROM A10_AccountPledges A10
      left join A01_AccountMaster A01 on a01.AccountNumber = a10.AccountNumber
      where A01.[Status] = 'A'