Re: [sqlite] select max(x), y from table
On Feb 4, 2013, at 12:47 PM, Ryan Johnsonwrote: > 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
On 03/02/2013 10:31 AM, Gabor Grothendieck wrote: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasmawrote: 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
On 4 Feb 2013, at 2:38am, Igor Tandetnikwrote: > 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
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
Op 3 feb 2013, om 22:06 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 4:00 PM, Petite Abeillewrote: 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
On Feb 3, 2013, at 9:54 PM, Gabor Grothendieckwrote: > 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
Op 3 feb 2013, om 20:01 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 1:40 PM, E.Pasmawrote: 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
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
Op 3 feb 2013, om 16:31 heeft Gabor Grothendieck het volgende geschreven: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasmawrote: 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
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
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