zen zen - 5 months ago 11
MySQL Question

Select last record of each person

I have the following tables

tbl_investors

id | first_name | last_name |
---------------------------------------
1 | Jon | Cold |
2 | Rob | Ark |
3 | Rickon | Bolt |


tbl_investors_ledger

id | investor_id | amount |
------------------------------------
1 | 1 | 500 |
2 | 2 | 200 |
3 | 2 | 250 |
4 | 2 | 300 |
5 | 3 | 10 |
6 | 1 | 550 |
7 | 3 | 20 |


I just want to return all investors with their latest amount. Ex, Jon Cold with 550, Rob Ark 300 and Rickon Bolt 20, alphabetically with their last name.

I have an existing query but it will not return the latest amount of the investor. Can someone help me what i'm doing wrong?

SELECT t1.*, t2.*
FROM ".tbl_investors." t1
LEFT JOIN ".tbl_investors_ledger." t2
ON t1.id = t2.investor_id
LEFT JOIN (SELECT t.investor_id, max(t.id) as tid
FROM ".tbl_investors_ledger." t ) tt
ON tt.investor_id = t2.investor_id AND tt.tid = t2.id
GROUP BY t2.investor_id
ORDER BY t1.last_name

Answer

Try this;)

 SELECT t1.*, t2.*
 FROM tbl_investors t1
 LEFT JOIN tbl_investors_ledger t2
 ON t1.id = t2.investor_id
 INNER JOIN (
     SELECT t.investor_id, max(t.id) as tid 
     FROM tbl_investors_ledger t GROUP BY t.investor_id) tt
 ON tt.investor_id = t2.investor_id AND tt.tid = t2.id
 ORDER BY t1.last_name

SQLFiddle DEMO

And check related OP Retrieving the last record in each group and this blog How to select the first/least/max row per group in SQL, you can find more solutions for your question.