SQL don't show duplicates/only show last value

Question

This is my query to get a bit of the customers data and their balance

SELECT c.id, a.fk_cust, c.firstname, c.lastname, t.cust_count, t.cust_balance
FROM addr a
INNER JOIN cust c ON a.fk_cust = c.id
INNER JOIN trans t ON c.id = t.fk_cust
WHERE c.id = t.fk_cust
ORDER BY lastname ASC

example of what the output looks like:

id fk_cust firstname lastname   cust_count cust_balance
1     1    test      customer1  1          0.32
1     1    test      customer1  2          0.64
2     2    test      customer2  1          0.74
3     3    test      customer3  1          0.23
3     3    test      customer3  2          0.56

what I want the output to look like>

id fk_cust firstname lastname   cust_count cust_balance
1     1    test      customer1  2          0.64
2     2    test      customer2  1          0.74
3     3    test      customer3  2          0.56

cust_count is the times that the custome has bought something. Now the thing is that I don't need the values from their past purchases but only the last/current balance. So how do I specify that I only want the last value from each customer?

Answer 1

If you are running MySQL 8.0, you can rank the transactions of each customer by descending cust_count in a subquery, then use that information to retain the latest transaction only:

SELECT c.id, a.fk_cust, c.firstname, c.lastname, t.cust_count, t.cust_balance
FROM addr a
INNER JOIN cust c ON a.fk_cust = c.id
INNER JOIN (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY fk_cust ORDER BY cust_count DESC) rn
    from trans t
) t ON c.id = t.fk_cust
WHERE r.rn = 1
ORDER BY lastname ASC

In earlier versions:

SELECT c.id, a.fk_cust, c.firstname, c.lastname, t.cust_count, t.cust_balance
FROM addr a
INNER JOIN cust c ON a.fk_cust = c.id
INNER JOIN trans t ON c.id = t.fk_cust AND r.rn = 1
WHERE t.cust_count = (SELECT MAX(t1.cust_count) FROM trans t1 WHERE t1.fk_cust = c.id)
ORDER BY lastname ASC