Question 20089 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Join to the latest record in mySQL

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
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    1. LATERAL JOIN. Just a docs link, because it’s less effficient and (for MySQL) more awkward to write.
    2. 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.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search