How can I get last transaction details for every customer in Mysql?

Question

I am trying to get the latest transaction for each customer from a Mysql database where each customer may have a different number of transaction records.

Here Mysql table:

This table i have mentioned the rows which have bold (style), these bold rows are last transaction records.I want every customer last transaction.

I except answer is below one.

I need mysql query for this selected records.

Answer 1

You'll want to take the MAX of your transaction date to find the most recent transaction. As this is an aggregate function you'll also want to GROUP BY your cus_id. This result then gives you the latest date for a customer so you can then join the rest of the data against that cus_id and tranc_date combination.

The query will look something like this:

SELECT cus_tranc.cus_id,
       cus_tranc.tranc_amt,
       cus_tranc.tranc_type,
       cus_tranc.tranc_date
FROM cus_tranc 
INNER JOIN (
  SELECT cus_id,
         MAX(tranc_date) AS 'tranc_date'
  FROM cus_tranc
  GROUP BY cus_id) max_tranc ON cus_tranc.cus_id = max_tranc.cus_id AND cus_tranc.tranc_date = max_tranc.tranc_date

You can see the results of this in this SQL Fiddle.