Igor Tandetnik wrote, On 2/2/2012 10:50 AM:
Petite Abeille<petite.abei...@gmail.com> wrote:
On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote:
ORDER BY applies to groups, not to rows within each group (is this different
with Oracle?)
analytic functions works in term of the result set itself.
Here is a simple example:
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
http://www.oracle-base.com/articles/misc/AnalyticFunctions.php
So there aren't actually FIRST and LAST aggregate functions, it seems - at
least not the way the OP envisions them. The syntax is completely different.
I'm OK with what you came up with. I think I need to fix the 1st part of
the between strftime, making the 1st arg '%Y-%m-01' instead of
'%Y-%m-01'. Anyway, it works and I don't think it's too gangly, at least
when formatted neatly. I just wish I was smart enough to have come with
that :) Maybe someday!
(select FOO from MyTable t2
where t2.date between strftime('%Y-%m-01',t.date) and
strftime('%Y-%m-31',t.date)
order by t2.date asc limit 1) AS FIRST_FOO,
Thanks!!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
