[eluser]Ichabodcole[/eluser]
Having some trouble figuring out how to generate an SQL query. I'm not sure if it's possible to do with one query or not so looking for help with that. The problem is described below.
I have a table that has a many to one relationship with another table, this child table has rows for a timestamp that is updated to the current time whenever the entry is updated and an parent_id referencing the primary id of the other table it belongs too.
So for example if the parent table has an id of 1 and there are 3 entries in the child table with a parent_id of 1 I only want to return the child in the result which has the newest timestamp.
Now there are several parent entries and therefore many child entries, and I want to return a result that just includes the children with the newest timestamp for each parent.
So I want to get something like the below out:
Child 1 with parent_id 1 and time 08 ->this child shows up in the result
Child 2 with parent_id 1 and time 06 ->this one does not
Child 3 with parent_id 2 and time 03 ->this one does not
Child 4 with parent_id 2 and time 12 -> this child shows up in the result
Child 5 with parent_id 2 and time 09 ->this one does not
Is there a way to do this with one query? I was trying to use GroupBy parent_id and order by timestamp, but no such luck, can't control which child shows up in the GroupBy as far as I can tell.
If any more explanation is necessary please let me know.
Oh also note, I do not necessarily need the parent table info in the result if I can just run the query on the child table.
Many thanks for any help!
