Query by row_number() in Sqlalchemy

Question

I have a query in raw SQL

select * from (select ROW_NUMBER() over(partition by
"categoryId" order by "updatedAt" desc) as "RowNum", 
"categoryId", "updatedAt" from products as tdr) a
where "RowNum"=1

How can I perform the same query in Flask Sqlalchemy?

I tried different approaches, and found a better solutions in Miguelgrinberg's Cookbook

Answer 1

Similar to the examples in the tutorial

https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-window-functions

your SQLAlchemy Core query would be

tdr = select(
    func.row_number()
    .over(partition_by=products.c.categoryId, order_by=products.c.updatedAt.desc())
    .label("RowNum"),
    products.c.categoryId,
    products.c.updatedAt,
).subquery("tdr")
qry = select(text("*")).select_from(tdr).where(tdr.c.RowNum == 1)
engine.echo = True
with engine.begin() as conn:
    rows = conn.execute(qry).all()
"""
SELECT * 
FROM (SELECT row_number() OVER (PARTITION BY products."categoryId" ORDER BY products."updatedAt" DESC) AS "RowNum", products."categoryId" AS "categoryId", products."updatedAt" AS "updatedAt" 
FROM products) AS tdr 
WHERE tdr."RowNum" = %(RowNum_1)s
[generated in 0.00046s] {'RowNum_1': 1}
"""