I have a parent table “A” looking like
and a child table “B” (related by ‘Main’) looking like
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?
You can create a
CTE (Common Table Expression) and generate a
PARTITION BY and ordering them by
Timestamp descending. If this
Rank equals 1, it means it’s the latest record for that
It might sound a bit confusing, but have a look if you only run the SQL in the
Then use this rank in the
WHERE statement to only look at the latest
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'