Re: [sqlite] select max(x), y from table

[Petite Abeille]

On Feb 4, 2013, at 12:47 PM, Ryan Johnson  wrote:

> Otherwise, it looks like you want some variant of the "windowing" and "rank" 
> functions, which are non-standard features of postgres:
> http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Window functions (aka analytics) are standard as per SQL:2003.

Many databases implement them.

Sadly, SQLite is not one of them. Oh, well...



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

Re: [sqlite] select max(x), y from table

[Ryan Johnson]

On 03/02/2013 10:31 AM, Gabor Grothendieck wrote:

On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:



On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:


http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group by
Name having max(Timestamp)", and the expectation, somehow, is that the
HAVING clause would cause each group to be represented by a row for which
max(Timestamp) is reached. I'm not sure where this expectation comes from.
This is a valid SQL statement whose HAVING clause means "only include a
group in the resultset if max(Timestamp) for this group is logically true"
(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward compatible -
it modifies the meaning of existing valid statements. Also, I'm pretty sure
it's not supported by any SQL standard; and I'm not aware of any DBMS that
would interpret the statement the way you want (which doesn't mean none such
exists, of course).

All in all, It seems unlikely that such a proposal would be entertained.
--
Igor Tandetnik


Hi,

Is it then not a perfect solution? it works, if in the example the timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might write
something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was recently
observed with this featurre (see

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question is still
if it will work also in future versions of SQLite.

What was wanted was to pick out the row with the largest timestamp in
each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.
select mytable.*, mt from mytable join (select id,name, max(timestamp) 
mt from mytable group by id,name) using(id)


Slightly cumbersome, but it works just fine... probably even fairly 
efficient thanks to use of a primary key index.


Otherwise, it looks like you want some variant of the "windowing" and 
"rank" functions, which are non-standard features of postgres:

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

That said, I'm not sure how to express your query more efficiently even 
using the pgsql syntax, as I understand the latter.


Ryan

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

Re: [sqlite] select max(x), y from table

[Simon Slavin]

On 4 Feb 2013, at 2:38am, Igor Tandetnik  wrote:

> On 2/3/2013 1:58 PM, Simon Slavin wrote:
>>> SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)
>> 
>> Your query is not standard SQL92.  SQL92 does not allow SELECT commands as 
>> part of expressions.
> 
> Does too:
> 
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> 
> 6.11  ::= ... |  | ...
> 7.11   ::= 
>  ::=   

I stand -- sprawl -- corrected.  I misread the documentation.  The 1999 spec 
introduced /named/ subqueries not subqueries.  Thanks for the correction.

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

Re: [sqlite] select max(x), y from table

[Igor Tandetnik]

On 2/3/2013 1:58 PM, Simon Slavin wrote:

SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)


Your query is not standard SQL92.  SQL92 does not allow SELECT commands as part 
of expressions.


Does too:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

6.11  ::= ... |  | ...
7.11   ::= 
 ::=   

--
Igor Tandetnik

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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 22:06 heeft Gabor Grothendieck het volgende  
geschreven:


On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille  wrote:


On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck  wrote:



What is the SQLite consortium?


http://www.sqlite.org/consortium.html
___


So is E. Plasma saying that there is a private version of sqlite that
has the feature in question but the free version of sqlite that the
rest of us have does not have that feature?


As you said:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x..

And that is about the free version. Not all that is gold does glitter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select max(x), y from table

[Petite Abeille]

On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck  wrote:

> What is the SQLite consortium?

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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende  
geschreven:



On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma  wrote:


Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende  
geschreven:




On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:



Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende  
geschreven:




On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:



In 3.7.11 there was a change to support the feature in the  
subject
which refers to guaranteeing that y comes from the same row  
having

maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as  
the

query here:



http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html




The query in that thread is of the form "select * from MyTable  
group by
Name having max(Timestamp)", and the expectation, somehow, is  
that the
HAVING clause would cause each group to be represented by a row  
for

which
max(Timestamp) is reached. I'm not sure where this expectation  
comes

from.
This is a valid SQL statement whose HAVING clause means "only  
include a
group in the resultset if max(Timestamp) for this group is  
logically

true"
(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward  
compatible

-
it modifies the meaning of existing valid statements. Also, I'm  
pretty

sure
it's not supported by any SQL standard; and I'm not aware of any  
DBMS

that
would interpret the statement the way you want (which doesn't  
mean none

such
exists, of course).

All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik




Hi,

Is it then not a perfect solution? it works, if in the example the
timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might  
write

something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was  
recently

observed with this featurre (see

http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The  
question is

still
if it will work also in future versions of SQLite.



What was wanted was to pick out the row with the largest timestamp  
in

each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.




OK, but if one does not assume any specific (non SQL standard)  
features, the

query is something like:

   SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE  
t2.y =

t.y)

If one looks for a shortcut for this quite common query, then the  
equivalent

SQLite query is really attractive:

   SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL



This seems to be going around in circles. These two are not equivalent
unless there has been some specific feature in sqlite to guarantee
that they are the same.   This was my original question.

If x is never null then the first gives the row in each group with
largest x

Indeed



and the second only gives an arbitrary row from each group
although it may in specific instances give the same result by chance.
True but not by chance. The feature is supported for a member of the  
SQLite consortium and applies if there is only a single aggregate  
function. Special is the example is that the aggregate function comes  
in the HAVING clause. But that appears to work fine too. I hope that  
the customer who requested this feature agrees that it should remain so


Thanks, EPasma.

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

Re: [sqlite] select max(x), y from table

[Simon Slavin]

On 3 Feb 2013, at 6:40pm, "E.Pasma"  wrote:

> OK, but if one does not assume any specific (non SQL standard) features, the 
> query is something like:
> 
>   SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)

Your query is not standard SQL92.  SQL92 does not allow SELECT commands as part 
of expressions.  Subqueries weren't introduced into SQL until SQL99.  Very few 
of the engines you see in popular use today implement all the features of 
SQL99, they're mostly supersets of SQL92.

> If one looks for a shortcut for this quite common query, then the equivalent 
> SQLite query is really attractive:
> 
>   SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL

That is a way of including only groups where NONE of the x values in the group 
are NULL.  Is that what you want ?

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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende  
geschreven:



On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma  wrote:


Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:



On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:


In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:


http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html



The query in that thread is of the form "select * from MyTable  
group by
Name having max(Timestamp)", and the expectation, somehow, is that  
the
HAVING clause would cause each group to be represented by a row  
for which
max(Timestamp) is reached. I'm not sure where this expectation  
comes from.
This is a valid SQL statement whose HAVING clause means "only  
include a
group in the resultset if max(Timestamp) for this group is  
logically true"

(that is, not NULL, 0, empty string or empty blob).

A semantic change of the nature you envision is not backward  
compatible -
it modifies the meaning of existing valid statements. Also, I'm  
pretty sure
it's not supported by any SQL standard; and I'm not aware of any  
DBMS that
would interpret the statement the way you want (which doesn't mean  
none such

exists, of course).

All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik



Hi,

Is it then not a perfect solution? it works, if in the example the  
timestamp
is always logically true (i.e nit 0 or NULL). Otherwise you might  
write

something like HAVING MAX(timestamp) OR MAX(timestamp) IS NULL.

This construction comes in useful to deal with the issue as was  
recently

observed with this featurre (see

http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question  
is still

if it will work also in future versions of SQLite.


What was wanted was to pick out the row with the largest timestamp in
each group (and not to pick out those groups with a non-null maximum
timestamp) so the fact that the desired result was returned in the
example would seem to be a  coincidence assuming no specific feature
along these lines has been implemented in SQLite.




OK, but if one does not assume any specific (non SQL standard)  
features, the query is something like:


SELECT * FROM t WHERE x = (SELECT MAX (x) FROM t t2 WHERE t2.y = t.y)

If one looks for a shortcut for this quite common query, then the  
equivalent SQLite query is really attractive:


SELECT * FROM t GROUP BY y HAVING MAX (x) IS NOT NULL


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

Re: [sqlite] select max(x), y from table

[E.Pasma]

Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven:


On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:

http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group  
by Name having max(Timestamp)", and the expectation, somehow, is  
that the HAVING clause would cause each group to be represented by a  
row for which max(Timestamp) is reached. I'm not sure where this  
expectation comes from. This is a valid SQL statement whose HAVING  
clause means "only include a group in the resultset if  
max(Timestamp) for this group is logically true" (that is, not NULL,  
0, empty string or empty blob).


A semantic change of the nature you envision is not backward  
compatible - it modifies the meaning of existing valid statements.  
Also, I'm pretty sure it's not supported by any SQL standard; and  
I'm not aware of any DBMS that would interpret the statement the way  
you want (which doesn't mean none such exists, of course).


All in all, It seems unlikely that such a proposal would be  
entertained.

--
Igor Tandetnik


Hi,

Is it then not a perfect solution? it works, if in the example the  
timestamp is always logically true (i.e nit 0 or NULL). Otherwise you  
might write something like HAVING MAX(timestamp) OR MAX(timestamp) IS  
NULL.


This construction comes in useful to deal with the issue as was  
recently observed with this featurre (see


 http://www.mail-archive.com/sqlite-users@sqlite.org/msg74761.html

The construction does not involve any semantic change. The question is  
still if it will work also in future versions of SQLite.


Regards, EPasma







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

Re: [sqlite] select max(x), y from table

[Igor Tandetnik]

On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:

http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group by 
Name having max(Timestamp)", and the expectation, somehow, is that the 
HAVING clause would cause each group to be represented by a row for 
which max(Timestamp) is reached. I'm not sure where this expectation 
comes from. This is a valid SQL statement whose HAVING clause means 
"only include a group in the resultset if max(Timestamp) for this group 
is logically true" (that is, not NULL, 0, empty string or empty blob).


A semantic change of the nature you envision is not backward compatible 
- it modifies the meaning of existing valid statements. Also, I'm pretty 
sure it's not supported by any SQL standard; and I'm not aware of any 
DBMS that would interpret the statement the way you want (which doesn't 
mean none such exists, of course).


All in all, It seems unlikely that such a proposal would be entertained.
--
Igor Tandetnik

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