select max(x), y from table

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

select max(x), y from table

Gabor Grothendieck
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

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Igor Tandetnik-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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@.../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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Gabor Grothendieck
On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma <[hidden email]> 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@.../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.

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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@.../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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Simon Slavin-3

On 3 Feb 2013, at 6:40pm, "E.Pasma" <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Gabor Grothendieck
In reply to this post by E.Pasma
On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma <[hidden email]> 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 <[hidden email]> 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@.../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 and the second only gives an arbitrary row from each group
although it may in specific instances give the same result by chance.

---
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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@.../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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Gabor Grothendieck
On Sun, Feb 3, 2013 at 3:26 PM, E.Pasma <[hidden email]> wrote:

>
> Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende geschreven:
>
>
>> On Sun, Feb 3, 2013 at 1:40 PM, E.Pasma <[hidden email]> 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 <[hidden email]> 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@.../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
>

What is the SQLite consortium?

Does that refer some different private version of sqlite?

What is the situation with respect to the free version I have (which
was the original question)?

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Petite Abeille-2

On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck <[hidden email]> wrote:

> What is the SQLite consortium?

http://www.sqlite.org/consortium.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Gabor Grothendieck
On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille <[hidden email]> wrote:
>
> On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck <[hidden email]> 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?

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]
> > wrote:
>>
>> On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck <[hidden email]
>> > 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Igor Tandetnik-2
In reply to this post by Simon Slavin-3
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 <value expression primary> ::= ... | <scalar subquery> | ...
7.11  <scalar subquery> ::= <subquery>
<subquery> ::= <left paren> <query expression> <right paren>

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Simon Slavin-3

On 4 Feb 2013, at 2:38am, Igor Tandetnik <[hidden email]> 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 <value expression primary> ::= ... | <scalar subquery> | ...
> 7.11  <scalar subquery> ::= <subquery>
> <subquery> ::= <left paren> <query expression> <right paren>

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Ryan Johnson-10
In reply to this post by Gabor Grothendieck
On 03/02/2013 10:31 AM, Gabor Grothendieck wrote:

> On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma <[hidden email]> 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@.../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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Ryan Johnson-10
In reply to this post by Gabor Grothendieck
On 03/02/2013 4:06 PM, Gabor Grothendieck wrote:
> On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeille <[hidden email]> wrote:
>> On Feb 3, 2013, at 9:54 PM, Gabor Grothendieck <[hidden email]> 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?
I believe that he's saying that the feature exists because a paying
customer wants it, and Consortium rules suggest that this more or less
ensures the feature will never go away.

Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select max(x), y from table

Petite Abeille-2
In reply to this post by Ryan Johnson-10

On Feb 4, 2013, at 12:47 PM, Ryan Johnson <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users