Skip to content
Advertisement

Find Latest Child Record (With Conditions) Per Parent Record

I have 2 tables with a parent/child hierarchy and am trying to pull the ID for the latest child record for each parent record, but also with conditions applied on the child record that is being pulled.

Here’s what my tables look like:

Now, let’s say I want to pull ID of latest QuizAttempt for each Quiz that was attempted from 2021-01-01 00:00:00 to 2021-01-01 23:59:59 to look something like this:

Currently, I’m just doing a self-referential joins to compare IDs to pull the latest QC:

Which works for pulling the latest child record. But, when I add the date range conditions (like by adding attempts1.AttemptedAt BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 23:59:59'), I get empty results.

Advertisement

Answer

I would suggest window functions in a subquery:

Note that the date arithmetic is much simpler without having to deal with the time components.

EDIT:

You can also use a correlated subquery:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement