Grouping By column and sort by

Question

suppose we have 2 tables

@Table
export class Parent extends Model {
 @PrimaryKey
 @HasMany(() => child, { as: 'child', foreignKey: 'PARENT_ID' })
 @Column
 ID: number
 @Column
 NAME: string
 ....
}

@Table
export class Child extends Model{
 @PrimaryKey
 @Column
 ID: number
 @Column
 NAME: string
 @PrimaryKey
 @BelongsTo(()=>Parent,{ as: 'parent', targetKey: 'ID', foreignKey: 'PARENT_ID' })
 @Column
 PARENT_ID: number
 @Column
 DOB: Date
.....
}

And this is the Table Data

 ___________  ______________________________________
| PARENTS   || CHILDRENS                            |
| ID NAME   ||  ID  NAME    PARENT_ID   DOB         |
| 1 David   ||  1   Oscar   1   2018-05-12 06:28:52 |
| 2 John    ||  2   Zac     2   2018-08-25 10:48:34 |
| 3 Steve   ||  3   Greg    1   2019-03-15 16:58:22 |
|           ||  4   Samuel  3   2019-12-16 23:12:34 |
|           ||  5   James   2   2020-03-25 08:48:52 |
|           ||  6   Dan     1   2021-01-05 16:48:12 |
|___________||  7   Kelly   3   2021-07-19 14:25:54 |
             |  8   Gary    1   2021-11-10 14:23:12 |
             |______________________________________|

and I expect the outcome result to be

 ___________________________________________________________________________________
| RESULT                                                                            |
| Parent_ID      Parent_Name         Child_ID        Child_Name      DOB            |
| 1                David                8               Gary    2021-11-10 14:23:12 |
| 3                Steve                7               Kelly   2021-07-19 14:25:54 |
| 2                John                 5               James   2020-03-25 08:48:52 |
|___________________________________________________________________________________|

I would prefer an answer in Sequelize but even raw query on SQL would be nice since I'm quite stuck with this.

this is just a small scale example but in reality there are tens of thousands parents and hundreds of thousands children so I also paginate the result. so performance should matter too :)

Answer 1

This gets a list of parents with the dob of their youngest child and then joins to children again -

WITH parents (id, name) AS (
    SELECT 1, 'David' UNION ALL
    SELECT 2, 'John' UNION ALL
    SELECT 3, 'Steve'
), children (id, name, parent_id, dob) AS (
    SELECT 1, 'Oscar',  1, '2018-05-12 06:28:52' UNION ALL
    SELECT 2, 'Zac',    2, '2018-08-25 10:48:34' UNION ALL
    SELECT 3, 'Greg',   1, '2019-03-15 16:58:22' UNION ALL
    SELECT 4, 'Samuel', 3, '2019-12-16 23:12:34' UNION ALL
    SELECT 5, 'James',  2, '2020-03-25 08:48:52' UNION ALL
    SELECT 6, 'Dan',    1, '2021-01-05 16:48:12' UNION ALL
    SELECT 7, 'Kelly',  3, '2021-07-19 14:25:54' UNION ALL
    SELECT 8, 'Gary',   1, '2021-11-10 14:23:12'
)
SELECT *
FROM (
    SELECT p.*, MAX(c.dob) youngest
    FROM parents p
    JOIN children c
        ON p.id = c.parent_id
    GROUP BY p.id
) y
JOIN children c
    ON y.id = c.parent_id
    AND y.youngest = c.dob