MySQL INNER JOIN select only the first latest created row

Question

I've tried and searched all the day before post but I'm not able to figure out how to solve my problem.

I've a table essentially like this

ID | Name | RelatedTo | CreatedAt | Revision

Basically this track a document name with its content and if is which revision is (0,1,2,3,...) and if it's related to a "father" document (optional).

A document can have or not a revision

ID | Name | RelatedTo | CreatedAt | Revision
1  | A    |           | 2019-11-01| 0
2  | B    |           | 2019-11-01| 0
3  |      | 2         | 2019-11-04| 1

I was start from this post but I'm not able to make it working for my case.

I need to select the master record (ex A and B) and the last CreatedAt field (2019-11-01 for A, 2019-11-04 for B).

how are the two A and B related i understand 3 goes to 2 but 2 goes not to 1. — nbk, Nov 04 '19 at 16:52
See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) and note that while minimal, 3 rows is rarely enough to be representative. — Strawberry, Nov 04 '19 at 17:11
@nbk A document can have or not a revision B (2) have (3) A(1) doesn't have. AA and B are not related, are different document. Thanks — Hw.Master, Nov 11 '19 at 16:57
i added some more Lines to your example, i can see only a recursive loop in a stored procedure, you should have added the names and not used the relatedto column — nbk, Nov 11 '19 at 17:42
@Hw.Master any comment on the answers ? Did any of them helped you ? Would be nice to give a feedback after the question is asken. — VBoka, Nov 15 '19 at 07:10

ScaisEdge · Answer 1 · 2019-11-04T17:02:55.180

0

You could use a subquery for max date group by name

select m.* 
from  my_table m
inner join  (
  select  name, max(reatedAt) max_date
  from   (
    select  name, createdAt
    from my_table 
    union 
    select m1.name, m2.createdAt
    from my_table m1 
   inner join  my_table m2 ON m1.id = me.RelatedTo
  ) t
  group by name
)  t ON t.name  = m.name 
      and t.max_date  = m.CreatedAt

edited Nov 04 '19 at 17:02

answered Nov 04 '19 at 16:45

ScaisEdge

131,976
10
91
107

your example seem to return only one record, the document A – Hw.Master Nov 15 '19 at 09:59

score 0 · Answer 2 · answered Nov 04 '19 at 16:59

This is what you need:

select a.RelatedTo
       , max(a.name)
       ,  max(a.m) 
from (select RelatedTo
             , name
             , max(CreatedAt) m 
      from my_table 
      where RelatedTo is not null
      group by RelatedTo
               , name
      union all
      select id
             , name
             , max(CreatedAt) m 
      from my_table 
      where RelatedTo is null
      group by ID
            , name ) a
group by a.RelatedTo
order by a.RelatedTo

Here is the DEMO

MySQL INNER JOIN select only the first latest created row

2 Answers2