Aggregate function that selects the value of a column that corresponds to another successful aggregate functions match

Question

Sorry about the bad subject. I couldn't think of a better one and I'm in a hurry.

Say i have the following tables:

Parent

• Id
• Name
• ...

Child

• Id
• ParentId
• Age
• ...

There is a One-to-Many relation between parent and child.

Now I want to construct a query that returns all parents and their youngest child. I want the result to be like this:

Result

• Parent.Id
• Child.Id
• Child.Age

There should also be only one row per Parent.Id in the result.

Here is an image that explains what i want

This is where I am right now:

SELECT   
Parent.Id AS ParentId,
Child.Id AS ChildId, 
MIN(Child.Age) AS ChildAge -- OVER (PARTITION BY Parent.Id) AS ChildAge
FROM Parent JOIN Child ON Parent.Id = Child.ParentId
GROUP BY Parent.Id, Child.Id

What I would want is an aggregate function to put around Child.Id that would fetch the Child.Id that corresponds to the row that is MIN(Child.Age). I can't find any such thing and i can't seem to emulate the behavior.

Any help is much appreciated!

Answer 1

Your idea of using OVER with a PARTITION BY ParentId is in the right direction; however, rather than use it with MIN, use it with a ROW_NUMBER() to get at the whole row. This:

SELECT
    ParentId
    , Id
    , Age
    , ROW_NUMBER() OVER ( PARTITION BY ParentId ORDER BY Age ) AS rn
FROM Child

returns all children, along with a row number indicating their age order within children of the same parent, so we just need to filter that:

SELECT ParentId, Id, Age FROM (
    SELECT
        ParentId
        , Id
        , Age
        , ROW_NUMBER() OVER ( PARTITION BY ParentId ORDER BY Age ) AS rn
    FROM Child
) c   -- need to alias the subquery in order to SELECT from it
WHERE rn = 1


ParentId    Id          Age
----------- ----------- -----------
1           11          2
2           13          4
3           15          3
4           19          1

Note that while you ask for "all parents and their youngest child", you may want to consider what you want returned for records in Parent with no matching records in Child - my solution, along with all the others, will not include any information about such Parent records.

Answer 2

SELECT   
Parent.Id AS ParentId,
C.Id AS ChildId, 
C.Age AS ChildAge 
FROM Parent, CHILD C
WHERE PARENT.ID = C.ParentId
AND C.AGE = 
(
    SELECT MIN(AGE)
    FROM CHILD
    WHERE ParentId = C.ParentId

)