Select last record of each person

Question

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

KaeL · Answer 1 · 2016-06-23T07:53:45.973

2

You can use GROUP_CONCAT and SUBSTRING_INDEX together

SELECT I.*
    , SUBSTRING_INDEX(GROUP_CONCAT(L.amount ORDER BY L.id DESC), ',', 1) AS LastAmount
    FROM tbl_investors AS I
    LEFT JOIN tbl_investors_ledgers AS L
        ON L.investor_id = I.id
    GROUP BY I.id
    ORDER BY I.last_name

Here a demo from SQLFiddle, many thanks to @zakhefron :)

edited Jun 23 '16 at 07:53

answered Jun 23 '16 at 07:24

KaeL

3,639
2
28
56

having an error: Message: Undefined property: stdClass::$amount @KaeL – zen Jun 23 '16 at 07:29
1

Its working @KaeL . Different thinking. Demo for above query [SQL Fiddle](http://sqlfiddle.com/#!9/32620/5) – zakhefron Jun 23 '16 at 07:46
Haha thanks @zakhefron! I don't have access to sqlfiddle at the moment that's why I can't create one. I'm updating my answer with your link. – KaeL Jun 23 '16 at 07:51

score 1 · Accepted Answer · edited May 23 '17 at 12:16

1

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.

edited May 23 '17 at 12:16

Community

1
1

answered Jun 23 '16 at 07:17

Blank

12,308
1
14
32

thanks for your prompt reply @10086, it worked! I have this problem almost a day already and i tried my best not to ask. You just solve it like seconds!!! awesome, thank again! – zen Jun 23 '16 at 07:24
@zen You're welcome. And do more google will be helpful. – Blank Jun 23 '16 at 07:28
I tried, i'm quite new to this, anyhow u are awesome! – zen Jun 23 '16 at 07:32

Select last record of each person

2 Answers2