How to find parent's youngest child's name-CodePudding

I have a people table shown below.

CREATE TABLE people (
  id INTEGER NOT NULL PRIMARY KEY,
  motherId INTEGER,
  fatherId INTEGER,
  name VARCHAR(30) NOT NULL,
  age INTEGER NOT NULL,
  FOREIGN KEY (motherId) REFERENCES people(id),
  FOREIGN KEY (fatherId) REFERENCES people(id)
);

INSERT INTO people(id, motherId, fatherId, name, age) VALUES(1, NULL, NULL, 'Adam', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(2, NULL, NULL, 'Eve', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(3, 2, 1, 'Cain', 30);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(4, 2, 1, 'Seth', 20);

-- Expected output (in any order):
-- name    age
-- ----------------------------
-- Adam    20
-- Eve     20

-- Explanation:
-- In this example.
-- Adam and Eve are parents and have two children: Cain and Seth. Seth is younger and is 20 years old.

How to display the youngest child's age and name in every family? Any suggestions are appreciated.

CodePudding user response：

You can use ROW_NUMBER() function to number records.

This numbering is based on motherId,fatherId which is sorted by age column.

After that, you can extract the numbers 1 of each family.

Use this:

select id,motherId,fatherId,name,age from 
    (select *
            ,DENSE_RANK() over (partition by motherId,fatherId order by age asc  ) as rn
            from people where (motherId is not null and fatherId is not null  ))u
    where u.rn=1

Result:

id  motherId    fatherId    name    age
4   2           1           Seth    20