CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Bhargav Andipara
Bhargav Andipara

Reputation: 69

SQL Join to the latest record

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  |
+----+------+----------+-----------+

Upvotes: 4

Views: 11787

Answers (7)

Larry Lustig
Larry Lustig

Reputation: 51008

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 AS T1
INNER JOIN Table2 AS T2 ON T1.ID = T2.ID 
WHERE NOT EXISTS (
   SELECT * FROM Table2 AS T2B WHERE T2B.ID = T1.ID AND T2B.Date > T2.Date)

Upvotes: 2

user1191247
user1191247

Reputation: 12973

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

Upvotes: 0

Joshua Amarfio
Joshua Amarfio

Reputation: 1

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
)

Upvotes: 0

Erwin Smout
Erwin Smout

Reputation: 18408

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.

Upvotes: 0

extreme4all
extreme4all

Reputation: 326

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)

Upvotes: 0

Hasan Gholamali
Hasan Gholamali

Reputation: 633

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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;

Upvotes: 2

Related Questions