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

user_7890775 0
Viewed 52

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?

parent-child sql
2 Answers

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

user_155890100

If you are using auto-increment id's (the last record automatically is the most recent one), you can use ORDER BY id DESC and LIMIT 1. Basically, you will retrieve all rows in decreasing order (latest first) and retrieve only the first one thanks to the LIMIT. Hope I understood the problem!

user_119610360
Related