Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause

[Alexey Pechnikov]

And this is needed as replacement of the "distinct on" clause. I don't
know other way to translate many queries from PostgreSQL to SQLite.

2010/6/11 Igor Tandetnik :
> SQLite allows "naked" non-grouped columns as an extension (which is 
> occasionally useful).


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause

[Alexey Pechnikov]

2010/6/11 Igor Tandetnik :
> SQLite allows "naked" non-grouped columns as an extension (which is 
> occasionally useful).

And this is needed as replacement of the "distinct on" clause. I don't
know other way to translate many queries from PostgreSQL to SQLite.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause

[Alexey Pechnikov]

2010/6/11 Igor Tandetnik :
> Well, many SQL engines, as well as SQL-92 standard, do prohibit such a 
> syntax. According to the standard, in a statement using GROUP BY, any column 
> reference that appears in SELECT clause must also appear in GROUP BY clause 
> or be part of an argument of an aggregate function. SQLite allows "naked" 
> non-grouped columns as an extension (which is occasionally useful).

This is needed as replacement of the "distinct on" clause. I don't
know other way to translate many queries from PostgreSQL to SQLite.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause

[Igor Tandetnik]

Jay A. Kreibich  wrote:
> On Fri, Jun 11, 2010 at 11:30:43AM +0200, Benoit Mortgat scratched on the 
> wall:
> 
>> select col1, col2 from foo group by col1;
>> 
>> As you can see, that last query does not result in any error, however
>> col2 should not be selectable if not in the group by clause?A
>> 
>> Is this a feature, a known bug or an unknown one? If this is a
>> feature, is there a pragma to forbid such a syntax?
> 
>  While the results are not all that useful, there is nothing that says
>  you can't do this.  You can't outright prohibit non-GROUP BY column
>  references in the select header, since they can be passed to
>  aggregate functions.

Well, many SQL engines, as well as SQL-92 standard, do prohibit such a syntax. 
According to the standard, in a statement using GROUP BY, any column reference 
that appears in SELECT clause must also appear in GROUP BY clause or be part of 
an argument of an aggregate function. SQLite allows "naked" non-grouped columns 
as an extension (which is occasionally useful).
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users