title: SQLAlchemy: select most recent row for all ids in a single table with composite primary key
body: <p>I want to do <a href="https://stackoverflow.com/questions/33538271/sqlite-how-to-select-most-recent-record-for-each-user-from-single-table-with">this</a> but in
  SQLAlchemy. The only difference is that rather than only being able to get the most recent record, I want to be able to get the most recent record before a
  given timestamp. As long as I ensure rows are never deleted, this allows me to view the database as it was on a particular timestamp.</p>
  
  <p>Let's say my model looks like this:</p>
  
  <pre class="lang-py prettyprint-override"><code>from datetime import datetime
  from sqlalchemy import Column, Integer, DateTime
  from sqlalchemy.ext.declarative include declarative_base
  Base = declarative_base()
  class User(Base):
      __tablename__ = "users"
      id_ = Column("id", Integer, primary_key=True, index=True, nullable=False)
      timestamp = Column(DateTime, primary_key=True, index=True, nullable=False, default=datetime.utcnow())
      # other non-primary attributes would go here
  
  </code></pre>
  
  <p>And I have this <code>users</code> table (timestamps simplified):</p>
  
  <pre><code>| id_ | timestamp |
  -------------------
    0     1
    0     4
    0     6
    1     3
    2     7
    2     3
  </code></pre>
  
  <p>For example, if I request a snapshot at <code>timestamp = 4</code>, I want to get:</p>
  
  <pre><code>| id_ | timestamp |
  -------------------
    0     4
    1     3
    2     3
  </code></pre>
  
  <p>The best I can come up with is doing it procedurally:</p>
  
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  db_engine = create_engine(...)
  SessionLocal = sessionmaker(bind=db_engine, ...)
  db_session = SessionLocal()
  
  def get_snapshot(timestamp: datetime):
      all_versions = db_session.query(User).filter(User.timestamp &lt;= timestamp).order_by(desc(User.timestamp))
      snapshot = []
      for v in all_versions:
          if v.id_ not in (i.id_ for i in snapshots):
              snapshot.append(v)
      return snapshot
  </code></pre>
  
  <p>However, this gives me a list of model objects rather than a <code>sqlalchemy.orm.query.Query</code>, so I have to treat the result differently to standard queries in
  other parts of my code. Can this be done all in the ORM?</p>
  
  <p>Thanks in advance</p>