MAX Transaction for each Individual 1

[Woodbadge]

I have a table of individuals and another table of transactions relating to those individuals. I want to find the most recent transaction for each individual in the database.

I have tried the following but this only gives me data for the most recent transaction in the database, not the most recent for each individual

SELECT
      tblIndividuals.individual_id,
      tblIndividuals.forename,
      tblIndividuals.surname,
      tblTransactionsIndividuals.transaction_date,
      tblTransactionsIndividuals.amount,
      tblTransactionsIndividuals.transaction_id
FROM tblIndividuals
LEFT JOIN tblTransactionsIndividuals ON tblIndividuals.individual_id = tblTransactionsIndividuals.individual_id
WHERE tblTransactionsIndividuals.transaction_date =
     (SELECT MAX(tblTransactionsIndividuals.transaction_date) FROM tblTransactionsIndividuals)
     GROUP BY tblIndividuals.individual_id
ORDER BY tblIndividuals.renewal_date;

Would be really grateful if someone could point me in the right direction. Thanks

 

[rac2]

SELECT
      I.individual_id,
      I.forename,
      I.surname,
      TX.transaction_date,
      TX.amount,
      TX.transaction_id
FROM tblIndividuals I
LEFT JOIN (
    SELECT individual_id,
           MAX(transaction_date) 
             AS "DateLastTx"
    FROM tblTransactionsIndividuals
    GROUP BY individual_id
   ) a ON a.individual_id = I.individual_id

LEFT JOIN tblTransactionsIndividuals TX 
          ON TX.individual_id = a.individual_id
         AND TX.transaction_date = a.DateLastTx

ORDER BY I.renewal_date;

The subquery with the alias, a, finds the latest date for every individual. This date plus the individual id then act as a filter for the transaction rows. Only the transaction row with a matching date will be shown for each individual.



If there happen to be two transactions with the exact same date and time, there will be two rows for an individual. This can be handled with another subquery and an additional JOIN condition.

SELECT
      I.individual_id,
      I.forename,
      I.surname,
      TX.transaction_date,
      TX.amount,
      TX.transaction_id
FROM tblIndividuals I
LEFT JOIN (
    SELECT individual_id,
           MAX(transaction_date) 
             AS "DateLastTx"
    FROM tblTransactionsIndividuals
    GROUP BY individual_id
   ) a ON a.individual_id = I.individual_id

LEFT JOIN tblTransactionsIndividuals TX 
          ON TX.individual_id = a.individual_id
         AND TX.transaction_date = a.DateLastTx
         AND TX.transaction_id = (
                  SELECT MAX(transaction_id)
                  FROM tblTransactionsIndividuals 
                  WHERE transaction_date = a.DateLastTx
                    AND individual_id = a.individual_id
                 )

ORDER BY I.renewal_date;

This ensures one row per individual.

 

[Woodbadge]

Thanks RAC2, that's done excatly what I wanted and has help me better understand sub queries with summary functions. Really appreciate your help.

 

[Fahtrim]

In your example you are left joining to the transactionindividuals table but are canceling that left join with an equality predicate in the Where clause.

In the method above he is maintaing a left join.

Not sure how your data is set up, but from your description you want this to be a true join.

Perhaps same result as his first one above with less code:

SELECT
      a.individual_id,
      a.forename,
      a.surname,
      b.transaction_date,
      b.amount,
      b.transaction_id
 FROM  tblIndividuals             a
  JOIN tblTransactionsIndividuals b ON a.individual_id = b.individual_id
 Where b.transaction_date = (SELECT MAX(b.transaction_date) 
                               FROM tblTransactionsIndividuals c
                              Where c.individual_id = a.individual_id)
ORDER BY a.renewal_date;

How is your transaction date field defined, is his second option possible for that "date" field?

 

[rac2]

Fahtrim-

Your simplified query is of course the better solution.

Assuming that tblIndividuals has one row per individual, which is reasonable, but we dont know. I see no reason for the GROUP BY other than that possibility.

Regarding the "true join", do you mean inner join? In any case that makes an important difference, whether the requirement is to see only those with transactions, or to see all individuals.

But here is the real reason I am posting this morning-

...equality predicate...

There is a phrase we dont see often.