title: Get one from duplicates where bool is true or fallback if none
body: <p>I have the following data:</p>
  <pre><code>id | name  | some_value | active
  --------------------------------
   1 | test  | 1234       | false
   2 | toast | qwert      | false
   3 | test  | 4321       | true
   4 | toast | trewq      | false
  </code></pre>
  <p>I want to get a list where each name is &quot;unique&quot;. Additionally I want to get those where <code>active</code> is true and if no true value is found the last item from the database is returned.</p>
  <p>Currently I have the following:</p>
  <pre class="lang-py prettyprint-override"><code>#!/usr/bin/env python3
  import re
  from sqlalchemy import create_engine, Column, Integer, String, Boolean, and_, or_
  from sqlalchemy.orm import sessionmaker
  from sqlalchemy.sql import func
  from sqlalchemy.ext.declarative import as_declarative, declared_attr
  
  
  PATTERN = re.compile(r&quot;(?&lt;!^)(?=[A-Z])&quot;)
  engine = create_engine('sqlite:///:memory:', echo=True)
  
  
  @as_declarative()
  class Base:
      id
      __name__
  
      # Generate __tablename__ automatically
      @declared_attr
      def __tablename__(cls) -&gt; str:
          return PATTERN.sub(&quot;_&quot;, cls.__name__).lower()
  
  
  class Data(Base):
      id = Column(Integer, primary_key=True, index=True)
      name = Column(String, nullable=False)
      some_value = Column(String, nullable=False)
      active = Column(Boolean, nullable=False, default=False)
  
      def __repr__(self):
          return f&quot;{self.id} | {self.name} | {self.some_value} | {self.active}&quot;
  
      def __str__(self):
          return f&quot;{self.id} | {self.name} | {self.some_value} | {self.active}&quot;
  
  
  class CrudData:
      def __init__(self, model):
          self.model = model
  
      def get_auto_all_active(self, db):
          subq = db.query(
              self.model.name,
              func.max(self.model.id).label(&quot;maxid&quot;)
          ).group_by(self.model.name).subquery(&quot;groupedjob&quot;)
  
          q = db.query(self.model).join(
              subq,
              or_(
                  and_(
                      self.model.name == subq.c.name,
                      self.model.id == subq.c.maxid
                  ),
                  self.model.active == True
              )
          )
          return q.all()
  
  
  if __name__ == &quot;__main__&quot;:
      Base.metadata.create_all(engine)
      Session = sessionmaker(bind=engine)
      db = Session()
  
      d1 = Data(name=&quot;test&quot;, some_value=&quot;4321&quot;, active=True)
      d2 = Data(name=&quot;toast&quot;, some_value=&quot;qwert&quot;)
      d3 = Data(name=&quot;test&quot;, some_value=&quot;1234&quot;)
      d4 = Data(name=&quot;toast&quot;, some_value=&quot;trewq&quot;)
  
      db.add_all([d1, d2, d3, d4])
      db.commit()
  
      crud = CrudData(Data)
  
      for data in crud.get_auto_all_active(db):
          print(data)
  </code></pre>
  <p>Which leads to:</p>
  <pre><code>1 | test | 4321 | True
  3 | test | 1234 | False
  4 | toast | trewq | False
  </code></pre>
  <p>However I want:</p>
  <pre><code>1 | test | 4321 | True
  4 | toast | trewq | False
  </code></pre>
  <p>How do I solve this? I'm trying to solve this for like 1 hour or so. Any assistance is helpful.</p>
  <p>Edit: Made script fully executable</p>