MAX plus other data with JOIN

Question

This should be a simple one I reckon (at least I thought it would be when I started doing it a couple hours ago).

I am trying to select the MAX value from one table and join it with another to get the pertinent data.

I have two tables: ACCOUNTS & ACCOUNT_BALANCES

Here they are:

ACCOUNTS

 ACC_ID | NAME    | IMG_LOCATION
 ------------------------------------
   0    | Cash    | images/cash.png
   500  | MyBank  | images/mybank.png

and

ACCOUNT_BALANCES

ACC_ID | BALANCE | UPDATE_DATE
-------------------------------
  500  | 100     | 2017-11-10
  500  | 250     | 2018-01-11
  0    | 100     | 2018-01-05

I would like the end result to look like:

ACC_ID | NAME    | IMG_LOCATION        | BALANCE | UPDATE_DATE
----------------------------------------------------------------
 0     | Cash    |  images/cash.png    | 100     | 2018-01-05
 500   | MyBank  |  images/mybank.png  | 250     | 2018-01-11

I thought I could select the MAX(UPDATE_DATE) from the ACCOUNT_BALANCES table, and join with the ACCOUNTS table to get the account name (as displayed above), but having to group by means my end result includes all records from the ACCOUNT_BALANCES table.

I can use this query to select only the records from ACCOUNT_BALANCES with the max UPDATE_DATE, but I can't include the balance.

SELECT
    a.ACC_ID,
    a.IMG_LOCATION,
    a.NAME,
    x.UDATE
FROM
    ACCOUNTS a
RIGHT JOIN
    (
        SELECT
            b.ACC_ID,
            MAX(b.UPDATE_DATE) as UDATE
        FROM
            ACCOUNT_BALANCES b
        GROUP BY
            b.ACC_ID
    ) x
ON
    a.ACC_ID = x.ACC_ID

If I include ACCOUNT_BALANCES.BALANCE in the above query (like so):

SELECT
    a.ACC_ID,
    a.IMG_LOCATION,
    a.NAME,
    x.UDATE
FROM
    ACCOUNTS a
RIGHT JOIN
    (
        SELECT
            b.ACC_ID,
            b.BALANCE,
            MAX(b.UPDATE_DATE) as UDATE
        FROM
            ACCOUNT_BALANCES b
        GROUP BY
            b.ACC_ID, b.BALANCE
    ) x
ON
    a.ACC_ID = x.ACC_ID

The results returned looks like this:

ACC_ID | NAME    | IMG_LOCATION        | BALANCE | UPDATE_DATE
----------------------------------------------------------------
 0     | Cash    |  images/cash.png    | 100     | 2018-01-05
 500   | MyBank  |  images/mybank.png  | 100     | 2018-01-11
 500   | MyBank  |  images/mybank.png  | 250     | 2018-01-11

Which is obviously the case, since I'm grouping by BALANCE in the subquery.

I'm super hesitant to post this, as this seems exactly the kind of questions that's been answered n times, but I've searched around a lot, but couldn't find anything that really helped me.

This one has a really good answer, but isn't exactly what I'm looking for

I'm obviously missing something really simple and even pointers in the right direction will help. Thank you.

score 1 · Accepted Answer · answered Jan 11 '18 at 17:15

1

Try This

    ;WITH CTE
AS
(
    SELECT
       RN = ROW_NUMBER() OVER(PARTITION BY AC.ACC_ID ORDER BY AB.UPDATE_DATE DESC),
       AC.ACC_ID,
       NAME,
       IMG_LOCATION,
       BALANCE,
       UPDATE_DATE
       FROM ACCOUNTS AC
          INNER JOIN ACCOUNT_BALANCES AB
             ON AC.ACC_ID = AB.ACC_ID
)
SELECT
    *
    FROM CTE     
       WHERE RN = 1

answered Jan 11 '18 at 17:15

Jayasurya Satheesh

7,826
3
22
39

Yup! Spot on, thank you my friend! Guess I'll look into partitions now, thanks for the help :) – Marcos Foster Jan 11 '18 at 17:34

score 1 · Answer 2 · answered Jan 11 '18 at 17:48

I think the simplest method is outer apply:

select a.*, ab.*
from accounts a outer apply
     (select top 1 ab.*
      from account_balances ab
      where ab.acc_id = a.acc_id
      order by ab.update_date desc
     ) ab;

apply implements what is technically known as a "lateral join". This is a very powerful type of join -- something like a generalization of correlated subqueries.

score 0 · Answer 3 · answered Jan 11 '18 at 17:38

The accepted answer will work. However, for large data sets try to avoid using the row_number() function. Instead, you can try something like this (assuming update_date is part of a unique constraint):

SELECT
    a.Acc_Id,
    a.Name,
    a.Img_Location,
    bDetail.Balance,
    bDetail.Update_Date
FROM 
    @accounts AS a LEFT JOIN 
    (
        SELECT Acc_Id, MAX(Update_Date) AS Update_Date 
        FROM @account_balances AS b 
        GROUP BY Acc_Id
    ) AS maxDate ON a.Acc_Id = maxDate.Acc_Id   
    LEFT JOIN @account_balances AS bDetail ON maxDate.Acc_Id = bDetail.Acc_Id AND 
                                              maxDate.Update_Date = bDetail.Update_Date

MAX plus other data with JOIN

3 Answers3