All we need is an easy explanation of the problem, so here it is.
The parent table is storing the latest update time for machine2 and machine3, but the updates themselves are stored in the child table, and I would like to return the latest update time for each machine that is a check (latest "check" record of the machine in the child table by time).
I can join the two, but I don’t know how to state the condition, because I want to return other data which is in the parent table (e.g. tester)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can use a window function such as
ROW_NUMBER() inside of a CTE to generate a unique integer series partitioned by each
Parent_entry and ordered by the
time descending and then use that dataset to filter out the latest records where the
notes = 'check' like so:
WITH CTE_ChildTable_CheckOnly_Sorted AS ( SELECT [time], Parent_entry, ROW_NUMBER() OVER (PARTITION BY Parent_entry ORDER BY [time] DESC) AS SortId FROM ChildTable WHERE notes = 'check' -- Only "check" rows ) SELECT S.[time], PT.title, PT.tester FROM CTE_ChildTable_CheckOnly_Sorted AS S INNER JOIN ParentTable AS PT ON S.Parent_entry = PT.Parent_entry WHERE S.SortId = 1 -- Filters out only the latest rows by time
Note if your ParentTable has rows that don’t exist in the ChildTable, and you want to keep those rows in the final results, then you need to use an outer join instead of an inner, and will need to do an
S.[time] and replace with
PT.[time]. If all ParentTable rows have at least one correlating ChildTable row, then the above example is fine.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂