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

select max(c.dob),other fields from parents p
join childrens c
      on p.parentId = c.parentId
group by c.parentId
order by c.dob desc

try this.

Answer 2

Try running this query

SELECT  MAX(child.dob) AS dob, child.*, parent.*
    FROM  parents
    INNER JOIN  child  ON parent.ID = child.PARENT_ID
    GROUP BY  parent.ID
    ORDER BY  dob DESC;