I have a table with a list of trails that I’ve biked. I wanted to find out which trail I’ve biked the most. I used the following SQL query to find out:
SELECT name, count(name) AS ct FROM trails GROUP BY name ORDER BY ct DESC LIMIT 5
This gave me the 5 trails I’ve biked the most.
Translated to SQLAlchemy.
from sqlalchemy import func def most_common_trails(db: Session): return ( db.query(Trail) .group_by(Trail.name) .order_by(func.count(Trail.name).desc()) .limit(5) .all() )