MySQL join two table with the maximum value on another field

Question

I have two table account and balance

/---------------------\
| cid | name | mobile |
|---------------------|
|  1  | ABC  | 12345  |
|---------------------|
|  2  | XYZ  | 98475  |
\---------------------/

/----------------------------\
| date       | cid | balance |
|----------------------------|
| 2013-09-19 |  1  |   5000  |
|----------------------------|
| 2013-09-19 |  2  |   7000  |
|----------------------------|
| 2013-09-20 |  1  |    300  |
|----------------------------|
| 2013-09-20 |  2  |   4500  |
|----------------------------|
| 2013-09-21 |  2  |    600  |
\----------------------------/

I would like to join this two table and get the balance of the maximum date for a particular cid.

Output result as -

/--------------------------------------------\
| cid | name | mobile | date       | balance |
|--------------------------------------------|
|  1  | ABC  | 12345  | 2013-09-20 |   300   |
|--------------------------------------------|
|  2  | XYZ  | 98475  | 2013-09-21 |   600   |
\--------------------------------------------/

Himanshu · Accepted Answer · 2021-05-18T04:08:43.283

14

You need to use two sub-queries like this:

SELECT a.cid, a.name, a.mobile, b.date, b.balance
FROM account a 
JOIN
(
    SELECT b1.* FROM balance b1
    JOIN
    (
      SELECT cid, MAX(Date) As maxDate
      FROM balance
      GROUP BY cid
    ) b2
    ON b1.cid = b2.cid
    AND b1.date = b2.maxDate
) b
ON a.cid = b.cid;

Output:

CID	NAME	MOBILE	DATE	BALANCE
1	ABC	12345	September, 20 2013 00:00:00+0000	300
2	XYZ	98475	September, 21 2013 00:00:00+0000	600

See this SQLFiddle

Edit

As discussed in the comments, this query can also be written with only one subquery:

SELECT a.cid, a.name, a.mobile, b1.date, b1.balance 
FROM account a 
JOIN balance b1 ON a.cid = b1.cid     
JOIN (
    SELECT cid, MAX(Date) As maxDate 
    FROM balance 
    GROUP BY cid
) b2 
ON b1.cid = b2.cid 
AND b1.date = b2.maxDate

See the adjusted SQLFiddle

edited May 18 '21 at 04:08

answered Sep 21 '13 at 05:06

Himanshu

31,810
31
111
133

1

Thanks @hims056 I got my desire result but it can not be save as view. when i try to save as view i got an error message as -1349-View's SELECT contains a subquery in the from clause. Please kindly help me out. I need to save this query in VIEW. – newcomer Sep 21 '13 at 05:43
2

@newcomer - Views can not contain sub-query. However you can create views for each above sub-queries then use those views in the final view. [See some tips here.](http://stackoverflow.com/a/8428703/1369235) – Himanshu Sep 21 '13 at 05:51
1

Thank you, really! – piotr_cz Sep 29 '17 at 13:47
1

thanks a lot for this. been stuck on a similar scenario for over an hour – Xishan Oct 21 '19 at 09:26
1

@HimanshuJansari: Sorry, I know you've answered this rather long ago, but I've come across a similar problem and your answer helped me build my query. By a copy-paste mistake I came up with this solution: `SELECT a.cid, a.name, a.mobile, b1.date, b1.balance FROM account a JOIN balance b1 ON a.cid = b1.cid JOIN (SELECT cid, MAX(Date) As maxDate FROM balance GROUP BY cid) b2 ON b1.cid = b2.cid AND b1.date = b2.maxDate`. I'm testing it back and forth, get the same result and would just like to know, why your answer is better? Thanks – fun2life May 14 '21 at 16:34
@fun2life: Actually your query is better than mine. I am not sure why did I use two nested sub queries instead of just one at that time. But having less sub queries is definitely better. So your solution is much better than me. You may post your answer stating the same or may edit my answer as OP is not active so your solution can be found in the accepted answer. – Himanshu May 17 '21 at 05:24

Onel Sarmiento · Answer 2 · 2013-09-21T05:41:28.053

1

SELECT a.cid, a.name, a.mobile, MAX(b.date), b.balance 
FROM account AS a
INNER JOIN balance AS b
WHERE a.cid=b.cid 
GROUP BY cid;

Sorry I din't notice the balance column in 3rd table.

SELECT a.cid, a.name, a.mobile, b.date, b.balance 
FROM account AS a
INNER JOIN (
      SELECT c.date, c.cid, c.balance FROM balance AS c
      INNER JOIN (
            SELECT cid AS cid2, MAX(date) AS date2
            FROM balance
            GROUP BY cid2) AS d
ON c.cid=d.cid2 
AND c.date=d.date2
) AS b
ON a.cid=b.cid 
GROUP BY cid;--

edited Sep 21 '13 at 05:41

answered Sep 21 '13 at 05:04

Onel Sarmiento

1,608
3
20
46

Hello Leo, Using aggregate function without grouping *every* fields will give [random values](http://sqlfiddle.com/#!2/d80276/8) for those fields. – Himanshu Sep 21 '13 at 05:09

score -1 · Answer 3 · edited Dec 16 '13 at 19:25

-1

This following statement should get you the required result :

SELECT cid, name, mobile, MAX(date), blance  
FROM account
LEFT JOIN balance
ON account.cid = balance.cid
GROUP BY balance.cid

edited Dec 16 '13 at 19:25

Marcus Adams

53,009
9
91
143

answered Dec 16 '13 at 19:03

user3108497

1

MySQL join two table with the maximum value on another field

3 Answers3

Edit

Linked

Related