web2py select distinct values

Question

I have a table like col1, col2, col3, col4, col5. I want to select distinct values of col3 and my query looks like below:

db().select(db.table.col1, db.table.col3, distinct=db.table.col3)

The query fails.

Answer 1

Except maybe when using postgresql as mentioned by @Anthony, for all other relational databases (afaik) distinct cannot be applied to individual fields part of the resultset, other then all fields. Web2py relects this in the parameter use. This can be set to True, or to all fields requested. As is in the book, from which i quoted the relevant for your convenience.

If you use sqlite use the groupby as mentioned by Anthony and apply aggregates for example: db().select(db.table.col1.max(), db.table.col3.max(), groupby=db.table.col3)

Hope this helps.

From the web2py manual, aggregates section:

distinct

With the argument distinct=True, you can specify that you only want to select distinct records. This has the same effect as grouping using all specified fields except that it does not require sorting. When using distinct it is important not to select ALL fields, and in particular not to select the "id" field, else all records will always be distinct.

Here is an example:

>>> for row in db().select(db.person.name, distinct=True):
        print row.name

Alex
Bob
Carl
Notice that distinct can also be an expression for example:

>>> for row in db().select(db.person.name,distinct=db.person.name):
    print row.name

Alex
Bob
Carl

Answer 2

If you set distinct to an expression (such as a Field object), it results in the DISTINCT ON SQL statement, which I believe is not supported in SQLite. It should work in PostgreSQL, though. As an alternative, you can try:

db().select(db.table.col1, db.table.col3, groupby=db.table.col3)

Either way, though, you might be missing some distinct values of col1, as you are not doing any aggregation.