Clever Geek Handbook

Join the second table containing multiple records, take the last one

I have two tables:

person_id | name
1            name1
2            name2
3            name3

and the second table:

person_id | date     | balance
1           2016-03     1200                    ---- \
1           2016-04     700                     ----  > same person
1           2016-05     400                     ---- /
3           2016-05     4000

Given that person_id 1 has three records in the second table, how can I join the first one simply by taking the last record? (ie: balance 400 corresponding to date: 2016-05).

For example: query output:

person_id | name    | balance
1           name1     400
2           name2     ---
3           name3     4000

if it prefers simplicity over the complexity of the solution

+3


source to share


2 answers


A query that works for all database engines,



select t1.name, t2.person_id, t2.balance
from table1 t1
join table2 t2 on t1.person_id = t2.person_id
join
(
    select person_id, max(date) as mdate
    from table2
    group by person_id
) t3 on t2.person_id = t3.person_id and t2.date = t3.mdate
+5


source


The best way to do this is in any database that supports the standard ANSI window functions (most of them):



select t1.*, t2.balance
from table1 t1 left join
     (select t2.*,
             row_number() over (partition by person_id order by date desc) as seqnum
      from table2 t2
     ) t2
     on t1.person_id = t2.person_id and seqnum = 1;
+1


source






More articles:


All Articles