CODEWITHSUNDEEP
mysqlsubqueryleft-join
zen
zen

Reputation: 393

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

Upvotes: 1

Views: 208

Answers (2)

KaeL
KaeL

Reputation: 3659

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 :)

Upvotes: 2

Blank
Blank

Reputation: 12378

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.

Upvotes: 1

Related Questions