Calculate rank of single row using subquery

[Stephan Hügel]

I'm trying to calculate the rank of a particular (unique) row id by using a subquery:

I first calculate the total ranking for a table, Game (using 1.4.23):

    sq = (

        session.query(

            Game.id,

            Game.score,

            func.rank().over(order_by=Game.score.desc()).label("rank"),

        )

        .filter(Game.complete == True)

        .subquery()

    )

Then filter by the row ID I want (gameid):

    gamerank = (

        session.query(

            sq.c.id, sq.c.score, sq.c.rank

        )

        .filter(sq.c.id == gameid)

        .limit(1)

        .one()

    )

Game.score is a Float column. Is this the most efficient way to do this, or am I over-complicating it?

[Jonathan Vanasco]

> Is this the most efficient way to do this, or am I over-complicating it?

That roughly looks like code that I've implemented in the past.

If it works and you don't have issues, I wouldn't worry about efficiency.  Stuff like this will often vary based on the underlying table data - the structure, size, etc.  Adding indexes on columns can often improve performance a lot.

If you're really concerned on optimizing this, the typical approach is to focus on generating the target SQL query that works within the performance constraints you want, and then porting it to sqlalchemy by writing python code that will generate that same output.