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?

CodePudding user response：

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!

CodePudding user response：

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: