CODEWITHSUNDEEP
pythonsqldatabasesqlalchemy
Meet Taraviya
Meet Taraviya

Reputation: 889

SQLAlchemy - Max of column among rows with same value in other column

I have the following a Table DataUnit with columns session_id (INT) and timeStamp (INT). I want to find the maximum value of timeStamp for a given value of session_id. How to do this in SQLAlchemy?

I can simply query all rows with given session_id, order by timeStamp and take the first element, but what will be the more efficient way of doing it?

Table definition:-

class DataUnit(db.Model):
    __tablename__ = 'DataUnit'
    dataUnit_id = Column(Integer(), primary_key=True, autoincrement=True)
    session_id = Column(Integer(), ForeignKey('Session.session_id'))
    timeStamp = Column(BigInteger())

Upvotes: 0

Views: 1412

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Your database probably has the aggregate function MAX(), and SQLAlchemy offers a generic approach to using SQL function expressions, the special func object. Since you are interested in a scalar value, you can also employ Query.scalar(), which returns the first column of the first result row, or None. It'd seem like you use Flask-SQLAlchemy, since you inherit from db.Model, so here's a method of using Model.query with Query.with_entities():

session_id = 'insert session id here'

max_timestamp = DataUnit.query.\
    filter_by(session_id=session_id).\
    with_entities(db.func.max(DataUnit.timeStamp)).\
    scalar()

Efficiency of the query will depend on your database engine and possible indexes available.

Upvotes: 2

Related Questions