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