CODEWITHSUNDEEP
pythondatabasesqlalchemy
priestc
priestc

Reputation: 35280

sqlalchemy: distinct only on one column

My table looks like this:

    title      |    slug
------------------------------
The Title      | the-title
Another Title  | another-title
another title  | another-title

I want to select by distinct slug, but also want the title returned as part of the results. Which title gets returned for multiple matches, I don't care.

So my results look like this:

[('The Title', 'the-title'), ('Another Title', 'another-title')]

Upvotes: 3

Views: 13174

Answers (3)

shroud
shroud

Reputation: 755

You can also solve this with:

session.query(Table.title, Table.slug).distinct(Table.slug)

This also allows you to return additional fields (say you wanted to see Table.id) in the same .query() field list, without requiring a separate subquery.

Upvotes: 5

ken.ganong
ken.ganong

Reputation: 1028

Instead of using a distinct keyword, you should use an aggregate function with a group by clause. Consider the following SQL to solve your need:

SELECT MAX(title), slug
  FROM yourtable
GROUP BY slug

The group by clause will cause the query to return only one row per distinct value for slug. The max function is an aggregate function which will return the maximum value for title within each group. This will give you one of the titles and since you don't care which one, that should be all you need.

Upvotes: 3

Derek Litz
Derek Litz

Reputation: 10897

You probably want to do something like:

session.query(func.max(Table.title), Table.slug).group_by(Table.slug).all()

Upvotes: 6

Related Questions