Skip to content
Advertisement

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?

Advertisement

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: enter image description here

10 People found this is helpful
Advertisement