We are still updating the manual. DISTINCT ON, db().select(distinct=expression), is a new feature supported only by some databases. I do not recommend using it because its output is not completely deterministic. I suggest you use groupby instead.
On Sunday, 30 September 2012 14:34:42 UTC-5, Paul wrote: > > Hi, > > Following the online manual, in the DAL section, the example of using > distinct with substrings produces an invalid sql syntax with the default > sqlite backend database: > > >>> print db(db.auth_user).select(distinct = db.auth_user.first_name[:2]) > Traceback (most recent call last): > File "<console>", line 1, in <module> > File "C:\web2py\gluon\dal.py", line 8766, in select > return adapter.select(self.query,fields,attributes) > File "C:\web2py\gluon\dal.py", line 2094, in select > return super(SQLiteAdapter, self).select(query, fields, attributes) > File "C:\web2py\gluon\dal.py", line 1594, in select > return self._select_aux(sql,fields,attributes) > File "C:\web2py\gluon\dal.py", line 1559, in _select_aux > self.execute(sql) > File "C:\web2py\gluon\dal.py", line 1671, in execute > return self.log_execute(*a, **b) > File "C:\web2py\gluon\dal.py", line 1665, in log_execute > ret = self.cursor.execute(*a, **b) > OperationalError: near "ON": syntax error > >>> print db._lastsql > SELECT DISTINCT ON (SUBSTR(auth_user.first_name,1,(3 - 1))) auth_user.id, > auth_user.first_name, auth_user.last_name, auth_user.email, > auth_user.password, auth_u > ssword_key, auth_user.registration_id FROM auth_user WHERE (auth_user.id> 0); > >>> > > Paul > --
