SQl playing

.py

inner_query =  (DBSession.query(InnerCert).filter(InnerCert.csr_id == CSR.id)
                          .correlate(CSR)
                          .order_by(InnerCert.not_after.desc())
                          .limit(1)
                          .subquery("c"))
                          
middle_query = (DBSession.query(inner_query).select_from(CSR)
                                            .offset(0)
                                            .subquery("s"))
                                            
outer_query = DBSession.query(InnerCert.csr_id, InnerCert.not_after).select_from(middle_query)

.SQL

SELECT (c).csr_id, (c).not_before, (c).not_after FROM (SELECT (SELECT c FROM certificate c WHERE c.csr_id=csr.id ORDER BY c.not_after DESC LIMIT 1) AS c FROM csr offset 0) s;