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

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 wa开发者_如何学Gont 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!

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.

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

)

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

This one might work:

SELECT
ParentID,
ChildId,
ChildAge
FROM
(

SELECT   
Parent.Id AS ParentId,
Child.Id AS ChildId, 
MIN(Child.Age) AS ChildAge,   
Ranking  = ROW_NUMBER() OVER(PARTITION BY  ParentID order by Child.Age asc)  

FROM Parent JOIN Child ON Parent.Id = Child.ParentId
GROUP BY Parent.Id, Child.Id, Child.Age
)A
Where Ranking =1

You may try something like this:

SELECT parentid, id, age FROM child c
 WHERE age = (SELECT MIN(c2.age) FROM child c2 WHERE c2.parentid = c.parentid);

Note that you may find more than one child per parent (if they have the same, minimal age).