0

I've got this table structure

    +------+-------+--------------------------------------+
    | parent_id   | child_id   | sdate                             
    +------+-------+--------------------------------------+
    | 59           | 1         | 2018-01-01                                  
    | 59           | 2         | 2019-01-01                                  
    | 60           | 5         | 2016-01-01                                  
    | ...          | ...       | ...                                  
    +------+-------+--------------------------------------+

What I need is to get the parent_id, and the child_id with the highest date for that parent_id.

If I do this query :

select parent_id, child_id, max(sdate) from my_table group by parent_id;

I get 

| parent_id   | child_id   | max(sdate)                        
| 59           | 1         | 2019-01-01                                  
| 60           | 5         | 2016-01-01

So the date is good but the child_id for the first row should be 2.

Fofole
  • 3,398
  • 8
  • 38
  • 59
  • 1
    what is `LeaseKey` ? – John Joe Jan 25 '19 at 17:42
  • @JohnJoe edited – Fofole Jan 25 '19 at 17:43
  • This is because you're using improper grouping (which MySQL silently allows in your version). You group by something but don't specify which one of the other columns you want to MySQL just gives you one of them. You will need to write a bit more complicated query to get the results. – Sami Kuhmonen Jan 25 '19 at 17:43
  • You're pulling the max date, not the entire row of the max date. The parent and child id are not necessarily those of the record with the max date. You'd have to make it so in your query. – erik258 Jan 25 '19 at 17:45
  • Is every date unique for a given lease key? If so you can join the table back to your existing query on the date and lease. If not, things are harder. – erik258 Jan 25 '19 at 17:47
  • @DanFarrell no, the dates are not unique but I could just get any of the id's if the dates are the same. – Fofole Jan 25 '19 at 17:49

1 Answers1

3

You should use a join with the max date for parent_id 

select m.parent_id, m.child_id, m.date 
from  my_table m
inner join  (
    select parent_id, max(sdate)  max_date 
    from my_table 
  group by parent_id   

) t on t.max_date  = m.date  and t.parent_id = m.parent_id

You are using group by in improper way ( the column in group by don't match the not aggregated column in select) so you obtain unpredictable result

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • This seems really close, but I'm not sure why the grouping column changed. – erik258 Jan 25 '19 at 17:53
  • @DanFarrell explain better your comment .. what do you mean with " why the grouping column changed" .. answer updated .. with a (hope) clear select – ScaisEdge Jan 25 '19 at 17:54
  • @DanFarrell . the column child_id is return in unpredictable way (this ahappend with mysql version previous that 5.7) the db engine return the first casual value encountered during seeking for this column .. – ScaisEdge Jan 25 '19 at 17:59
  • Okay, i see the edit where the op changed the query now. Sorry, i was out of date. – erik258 Jan 26 '19 at 14:44