sqlalchemy - How to find position of User in query? -

how find position of user in query ? have ( using mysql)

session.query(usermodel).order_by(desc(usermodel.age)).all() 

and have user id, how find position of specific id ordered array ? ( can return , iterate there uicker way solve on database level, need run fast)

database should support window functions this. raw sql query this:

select pos     (select id, row_number() on (order age desc) pos user) sub     sub.id = :id; 

in sqlalchemy:

from sqlalchemy import func, desc  user_id = 42 sub = (session     .query(         usermodel.id,         func.row_number().over(order_by=desc(usermodel.age)).label('pos'))     .subquery()) pos = session.query(sub.c.pos).filter(sub.c.id==user_id).scalar() 

note returned index 1-based.

among popular rdbms work in postgresql, oracle, mssql, not in mysql or sqlite.