How can I return the latest child of a parent table in SQL

I have a parent table “A” looking like

Main	Status	Reference
1	0	AA
2	1	AB
3	0	AC
4	0	CA

and a child table “B” (related by ‘Main’) looking like

ID	Main	C-Status	Timestamp
1	1	c	3
2	1	b	4
3	2	a	4
4	2	b	5
5	2	c	6
6	3	c	3
7	4	b	5
8	4	c	8
9	4	a	9

I need to find all rows in table “A”, where the latest Status in table “B” has ‘C-Status’ with a Value of “C” and the ‘Status’ in table “A” is “1”

In this example it would be ‘Main’ “2” (ID 5). NOT “3” (ID 6) since ‘Status’ is not “1”

The output I would like “A.Main”, “B.C-Status”, “B.Timestamp”, “A.Reference”

I’ve been fighting with INNER JOINS and GROUP BY all my life… I just don’t get it. This would be for MS-SQL 2017 if that helps, but I’m sure it’s a simple thing and not essential?

Answer

You can create a CTE (Common Table Expression) and generate a RANK per Main using PARTITION BY and ordering them by Timestamp descending. If this Rank equals 1, it means it’s the latest record for that Main.

It might sound a bit confusing, but have a look if you only run the SQL in the WITH statement.

Then use this rank in the WHERE statement to only look at the latest C-status per Main

WITH CTE AS (
    SELECT 
       B.ID, 
       B.Main,
       B.C-Status, 
       B.Timestamp, 
       RANK() OVER (PARTITION BY B.Main ORDER BY B.Timestamp DESC) AS Rank
    FROM B
) 

SELECT 
   A.Main, 
   CTE.C-Status, 
   CTE.Timestamp, 
   A.Reference
FROM A
INNER JOIN CTE
ON A.ID = CTE.Main
WHERE A.Status = 1
AND   CTE.Rank = 1
AND   CTE.C-Status = 'c'

Also if you struggle with JOINS this little picture always helps: