SQLAlchemy: Selecting one row for each distinct value of a column

Question

I want to select one row each for each distinct value of a column. Here I want to perform it for col1:

| ID | COL1 | COL2 |
--------------------
|  1 |    0 |    0 |
|  2 |    0 |    1 |
|  3 |    1 |   11 |
|  4 |    1 |   12 |
|  5 |    1 |   16 |

Which results in:

| ID | COL1 | COL2 |
--------------------
|  1 |    0 |    0 |
|  3 |    1 |   11 |

as 0 and 1 were the distinct values for col1. I am not strict about which row is returned (no need of least value of id, for example) as long as distinct values of column is returned.

Please assume the model is called TestModel and everything is fully mapped.

Answer 1

You need to use sub-query for that. And also you have to choose MIN or MAX function:

SELECT * FROM TestModel
WHERE ID IN(SELECT MIN(id) 
            FROM TestModel 
            GROUP BY col1)

Output:

| ID | COL1 | COL2 |
--------------------
|  1 |    0 |    0 |
|  3 |    1 |   11 |

See this SQLFiddle

---

SA version of the solution:

subq = (session.query(func.min(TestModel.id).label("min_id")).
        group_by(TestModel.col1)).subquery()

qry = (session.query(TestModel).
       join(subq, and_(TestModel.id == subq.c.min_id)))

Answer 2

This will return TestModel objects for each distinct values of TestModel.col1 column, just like GROUP BY queries in SQL.

session.query( TestModel).group_by( TestModel.col1).all()

Answer 3

As of SQLAlchemy 1.0 you can call distinct with a criterion, which allows you to write

session.query(TestModel).distinct(TestModel.col1).all()

In Postgresql this will translate to a DISTINCT ON query.

This not only works with the ORM but also with a SQLAlchemy Core select.