How to correctly make a request with order_by and distict?

Question

SQL

I can not figure out how to form a simple request.
There is a parent, he can have children. You need to select children, one from each parent, the child must have the latest creation date. The order should be sorted in descending order by the creation date of the child.
Since the query is used in flask-sqlalchemy pagination, you need to have all this in the query.

class Parent(Model):

    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    chils = relationship(Child, backref="parent")


class Child(Model):

    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    parent_id = Column(ForeignKey('parent.id'))


p1 = Parent()
p1.childs.append(
    Child()
)
p1.childs.append(
    Child()
)

p2 = Parent()
p2.childs.append(
    Child()
)
p2.childs.append(
    Child()
)
db.session.add(p1)
db.session.add(p2)

query = Child.query
query = query.distinct(Child.parent_id)
query = query.order_by(Child.parent_id.desc(), Child.created_at.desc())
parents = query.all()

In this way they are chosen correctly, but the order is not the one that is needed: the date of creation of the child is random:
08/27/2017 14:00
08/27/2017 13:54
08/27/2017 13:55

Answer 1

Did so

query = Child.query
query = query.with_entities(func.max(Child.id))
query = query.group_by(Child.parent_id)
subquery = query.subquery()

query = Child.query
query = query.filter(Child.id.in_(subquery))
query = query.order_by(Child.id.desc())
childs = query.all()