Re: [sqlite] GROUP BY question

[Mike King]

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

Re: [sqlite] GROUP BY question

[Petite Abeille]

On May 24, 2012, at 7:22 PM, Mike King wrote:

> Is this Select statement valid?

In SQLite, yes.

>  In Oracle, it wouldn't be because
> what is the aggregate of A.  

Right. SQLite tries nonetheless to return "something" . A bit of a (mis)feature 
IMO.

> Is this behavior defined anywhere?

http://sqlite.org/lang_select.html#resultset

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

Re: [sqlite] GROUP BY question

[Pavel Ivanov]

On Thu, May 24, 2012 at 1:22 PM, Mike King  wrote:
> Is this Select statement valid?  In Oracle, it wouldn't be because
> what is the aggregate of A.  Is this behavior defined anywhere?
>
> create table T (A,B);
> insert into  T (A,B) values (1,3);
> insert into  T (A,B) values (2,3);
>
> select A,B
> from T
> group by B;

For SQLite this statement is valid but behavior is undefined - you can
get either 1 or 2 for A without any determinism.


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

Re: [sqlite] GROUP BY question

[Richard Hipp]

On Thu, May 24, 2012 at 1:22 PM, Mike King  wrote:

> Is this Select statement valid?  In Oracle, it wouldn't be because
> what is the aggregate of A.  Is this behavior defined anywhere?
>
> create table T (A,B);
> insert into  T (A,B) values (1,3);
> insert into  T (A,B) values (2,3);
>
> select A,B
> from T
> group by B;
>

This is equivalent to the PostgreSQL:

SELECT DISTINCT ON(b) a,b FROM t;

There have been numerous complaints about the syntax and the fact that it
does not generate an error like Oracle, but there are also many legacy
applications that depend on this behavior.  So we cannot change it without
breaking applications and causing general grief and woe amongst developers.


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



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

[sqlite] GROUP BY question

[Mike King]

Is this Select statement valid?  In Oracle, it wouldn't be because
what is the aggregate of A.  Is this behavior defined anywhere?

create table T (A,B);
insert into  T (A,B) values (1,3);
insert into  T (A,B) values (2,3);

select A,B
from T
group by B;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users