SQL Join to the latest record

Question

I want to join tables in such a way that it fetches only latest record from one of the tables:

The following are my data

Table_One:

+----+------+
| ID | Name |
+----+------+
|  1 | John |
|  2 | Tom  |
|  3 | Anna |
+----+------+

Table_two:

+----+----------+-----------+
| ID | Visit ID |   Date    |
+----+----------+-----------+
|  1 |     2513 | 5/5/2001  |
|  1 |    84654 | 10/5/2012 |
|  1 |      454 | 4/20/2018 |
|  2 |      754 | 4/5/1999  |
|  2 |      654 | 8/8/2010  |
|  2 |      624 | 4/9/1982  |
|  3 |     7546 | 7/3/1997  |
|  3 |   246574 | 6/4/2015  |
|  3 |    15487 | 3/4/2017  |
+----+----------+-----------+

Results needed after Join:

+----+------+----------+-----------+
| ID | Name | Visit ID |   Date    |
+----+------+----------+-----------+
|  1 | John |      454 | 4/20/2018 |
|  2 | Tom  |      654 | 8/8/2010  |
|  3 | Anna |   246574 | 6/4/2015  |
+----+------+----------+-----------+

Tag your question with the database you are using. – Gordon Linoff May 30 '18 at 12:51 — Gordon Linoff, May 30 '18 at 12:51

score 2 · Answer 1 · answered May 30 '18 at 13:00

Different database engines have varying ways to get the top row from table 2 per group (you can google for "SQL windowing functions" and your product). Since you don't state what engine you're using it's impossible to give the most appropriate or most performant solution.

The following method should work in most or all SQL engines but will not be especially performant over a large data set (it will benefit from a composite index Table2(ID, Date)). The details of how you specify the aliases for the tables may differ a bit among engines but you can use this as a guide. A windowing function solution will probably be more efficient.

 SELECT ID, Name, VisitID, Date FROM Table1 T1 INNER JOIN Table2 T2 +
   ON T1.ID = T2.ID 
   WHERE NOT EXISTS (SELECT * FROM Table2 T2B WHERE T2B.ID = T1.ID AND T2B.Date > T2.Date)

In 2018 all modern DBMS support window functions – May 30 '18 at 13:06 — , May 30 '18 at 13:06
In 2018, I work with several non-modern DBMS systems. – Larry Lustig May 30 '18 at 13:07 — Larry Lustig, May 30 '18 at 13:07
I am sorry, we are using SQL Server 2016 – Bhargav Andipara May 31 '18 at 01:47 — Bhargav Andipara, May 31 '18 at 01:47

score 2 · Answer 2 · answered May 30 '18 at 13:00

I suspect you have SQL Server if so, then you can use APPLY:

select o.*, tt.*
from Table_One o 
cross apply ( select top 1 t.VisitDate, t.Date
              from table_two t
              where t.id = o.id
              order by t.date desc
            ) tt;

score 1 · Answer 3 · answered May 30 '18 at 13:05

1

SELECT ID,Name,Visit_ID,Date
FROM
    (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID Date DESC) as seq
    FROM Table2 LEFT OUTER JOIN
         Table1 ON Table2.ID = Table1.ID) as mainTable
WHERE seq = 1

answered May 30 '18 at 13:05

Hasan Gholamali

633
4
13

This. The windowing function is nearly always the best option. – Joel Coehoorn Apr 13 '23 at 15:35

score 0 · Answer 4 · edited Apr 13 '23 at 15:27

You can filter out "latest visit" using

SELECT ID,MAX(DATE) FROM TABLE_TWO GROUP BY ID;

You then join that to TABLE_ONE (... ON .ID = <alias_of_your_aggregation>.ID) to pick up the Name column and then join that again to TABLE_TWO (... ON ID=ID AND DATE=DATE) if you need to pick up the VISIT_ID as well.

Specific DBMS's might have proprietary/idiosyncratic extensions typically serving the purpose of allowing the optimizer do a better job (e.g. allowing the optimizer to understand that the "joining back to TABLE_TWO can be eliminated). Thinking here of SELECT TOP 1 ... and the like.

extreme4all · Answer 5 · 2018-05-30T14:26:08.353

I'm not a 100% sure if this is correct since the Visit ID might just throw every record right back at you. However you can find some great documentation here: https://www.w3resource.com/sql/aggregate-functions/max-date.php

select t1.ID,t1.Name,t2.visit_ID, Max(t2.Date) from Table_Two t2
inner join Table_One t1
on(t2.ID=t1.ID)
group by t1.ID,t1.Name,t2.visit_ID

something like this should work though, i think that this is also the same as @Erwin Smout proposes

select a.ID, t1.Name, a.date,t2.Visit_ID (
select ID, max(date)'date' from Table_Two 
group by ID) a
inner join Table_One t1
    on( a.ID=t1.ID)
inner join Table_Two t2
    on(a.ID=t2.ID and a.Date=t2.Date)

score 0 · Answer 6 · edited Apr 13 '23 at 15:38

0

This is the answer to your question

SELECT t1.ID, t1.Name, t2.visit_id, t2.Date 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.ID = t2.ID 
WHERE NOT EXISTS (
    SELECT * FROM table2 t2b WHERE t2b.ID = t1.ID AND t2b.Date > t2.Date
)

edited Apr 13 '23 at 15:38

Joel Coehoorn

399,467
113
570
794

answered Mar 08 '22 at 13:15

Joshua Amarfio

1
1

user1191247 · Answer 7 · 2023-04-13T22:07:59.763

This is a working version of Hasan's answer:

SELECT ID, Name, [Visit ID], Date
FROM (
    SELECT
        t1.*, t2.[Visit ID], t2.Date,
        ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY Date DESC) AS rn
    FROM Table_One t1
    JOIN Table_Two t2 ON t1.ID = t2.ID
) AS t
WHERE rn = 1;

The output is:

ID	Name	Visit ID	Date
1	John	454	2018-04-20
2	Tom	654	2010-08-08
3	Anna	15487	2017-03-04

Note: The row returned for Anna is different to the one in the question, as the desired output is incorrect!

And here's a db<>fiddle

SQL Join to the latest record

7 Answers7

Linked