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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
