title: Postgres distinct on in sqlalchemy join: subquery must return only one column
body: <p>I have a table of posts and a table of comments on those posts. There is no foreign key constraint, but the post_id on a comment refers to the id on a post.</p>
  <p>I am using a query like this to get a list of recent posts with the text of the most recent comment on that post:</p>
  <pre><code>SELECT posts.*, comments.text
  FROM posts
  LEFT JOIN (
      SELECT DISTINCT ON (&quot;post_id&quot;) *
      FROM comments
      ORDER BY &quot;post_id&quot;, &quot;timestamp&quot; DESC
  ) comments ON posts.id=comments.post_id
  WHERE posts.timestamp &gt;= start_date;
  </code></pre>
  <p>I would like to convert this query to use with sqlalchemy expressions. I have tried this:</p>
  <pre><code>query = select(posts, comments.c.text).\
      join(comments, and_(select(comments).
          distinct(comments.c.post_id).
          order_by(comments.c.post_id, comments.c.timestamp.desc()),
          posts.c.id == comments.c.post_id), isouter=True).\
      where(posts.c.timestamp &gt;= start_date)
  </code></pre>
  <p>But it is creating a slightly different query:</p>
  <pre><code>SELECT posts.id, posts.[other posts columns...], comments.text
  FROM posts 
  LEFT OUTER JOIN comments ON (
      SELECT DISTINCT ON (comments.post_id) comments.text, comments.[other comments columns...]
      FROM comments 
      ORDER BY comments.post_id, comments.timestamp DESC
  ) AND posts.id = comments.post_id 
  WHERE posts.timestamp &gt;= start_date;
  </code></pre>
  <p>which results in an error: <code>sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery must return only one column</code></p>