SQL Join to the latest record in mySQL

Question

This does not solve my problem SQL Join to the latest record

I want to join tables in such a way that it fetches only the latest record from one of the tables using MySQL and also count the read_reciept columns with specific value

The following are my datas.

Table_One:

ID	Name
1	John
2	Tom
3	Anna

Table_two:

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

Results needed after Join:

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

Also: voted to reopen, because the duplicate question was for pre-8.0. For 8.0 and later there's a much better option. — Joel Coehoorn, Apr 13 '23 at 15:13
[Do any of these answer your question?](https://stackoverflow.com/questions/tagged/greatest-n-per-group+mysql) — user1191247, Apr 13 '23 at 15:52

score 2 · Accepted Answer · answered Apr 13 '23 at 15:16

2

Assuming you're up to an 8.0 release, you have two options:

LATERAL JOIN. Just a docs link, because it's less effficient and (for MySQL) more awkward to write.
Windowing function:

SELECT ID, Name,VisitID, Date
FROM (
    SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
        row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn
    FROM Table_One t1
    INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t 
WHERE rn = 1

If you're on an older release, well condolences first of all. 5.7 and older don't even really qualify as a modern database, and hasn't for a long time. But this is still possible.

answered Apr 13 '23 at 15:16

Joel Coehoorn

399,467
113
570
794

2

Here: https://stackoverflow.com/a/50605493/10498828 is the answer from the duplicate link that I proposed that you duplicated with your answer. – forpas Apr 13 '23 at 15:20
@forpas When the question was closed, it was pointing to a different question where the top answers (nearly 200 votes) still used the 5.7 way. I think there must have been another close vote with the other question that SO used instead. – Joel Coehoorn Apr 13 '23 at 15:24
I used another question as a duplicate link to close the question and tried to edit more (because there are several such questions). But you opened the question in less than 1 min after it was closed. – forpas Apr 13 '23 at 15:29
Hmm... having read through the other question, it's also referring to SQL Server rather than MySQL, and the top answer there still suggests APPLY vs row_number(). – Joel Coehoorn Apr 13 '23 at 15:36
But holy cow: it's even the same sample data! This has to be a homework assignment, in which case `row_number()` might not be what the professor expects ( undergrad homework tends for want to join to a subquery instead). – Joel Coehoorn Apr 13 '23 at 15:40
@Joel Coehoorn I would also like to count the read_reciept columns when it has a specific value. I have updated the question can you please assist me with this? – devsam247 Apr 14 '23 at 11:47

SelVazi · Answer 2 · 2023-04-14T12:10:09.140

1

If you are using an old version of mysql then try this :

Steps to do it :

First we need to get the latest date :

select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
from Table_two
group by ID

Then we get the related Visit_ID and sum(read_reciept):

SELECT a.ID, a.Visit_ID, t.max_date, t.read_reciept
FROM Table_two a
INNER JOIN (
  select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
  from Table_two
  group by ID
) as t on a.ID = t.ID and a.Date = t.max_date

And Finally we join our data using INNER JOIN to get the expected result

SELECT a.ID, b.Name, a.Visit_ID, a.Date, t.read_reciept
FROM Table_two a
INNER JOIN Table_One b on a.ID = b.ID
INNER JOIN (
   select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
   from Table_two
 group by ID
) as t on a.ID = t.ID and a.Date = t.max_date

Demo here

edited Apr 14 '23 at 12:10

answered Apr 13 '23 at 15:42

SelVazi

10,028
2
13
29

I would also like to count the read_reciept columns when it has a specific value. I have updated the question. Can you please help me with it? – devsam247 Apr 14 '23 at 11:50
I have updated my answer, can you have a look – SelVazi Apr 14 '23 at 12:10
Can you update the demo, let me have a look, please! – devsam247 Apr 14 '23 at 12:24
Already updated it – SelVazi Apr 14 '23 at 12:27
Thanks, but when I adopted your solution for my large database I got a different result with @joel Coehoorn solution I got the expected result but without the read_reciept count. – devsam247 Apr 14 '23 at 13:23

score 0 · Answer 3 · answered Apr 13 '23 at 15:15

Use sub-query with row_number to get only the last row from that table_two.

select Table_One.ID, name, Visit_ID, date_
from Table_One
join (select ID, Visit_ID, date_, row_number() over (partition by id order by date_ desc) rn
      from Table_two) t2
on (t2.ID = Table_One.ID and t2.rn=1)

Here row_number will number row with the same ID based on order by date column.

Later during join we take only rows with the value being produced equal 1, which means in this case - oldest.

SQL Join to the latest record in mySQL

3 Answers3