On 03/02/2013 10:31 AM, Gabor Grothendieck wrote:
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.
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
