Bill McCormick <wpmccorm...@gmail.com> wrote:
> SELECT
> strftime('%Y-%m',date) AS YEAR_MONTH,
> SUM(BAR) AS SUM_BAR,
> FIRST(FOO) AS FIRST_FOO,
> LAST(FOO) AS LAST_FOO
> GROUP BY YEAR_MONTH
> ORDER BY DATE
>
> ... for a data set with the following columns: item|date|bar|foo.
> Basically, we want to know the value of foo at the first and last
> records of the month.
No, SQLite doesn't have this. ORDER BY applies to groups, not to rows within
each group (is this different with Oracle?) Something like this should work
(but is kinda hard on the eyes):
SELECT
strftime('%Y-%m',date) AS YEAR_MONTH,
SUM(BAR) AS SUM_BAR,
(select FOO from MyTable t2
where t2.date between strftime('%Y-%m',t.date) and
strftime('%Y-%m-31',t.date)
order by t2.date asc limit 1) AS FIRST_FOO,
(select FOO from MyTable t2
where t2.date between strftime('%Y-%m',t.date) and
strftime('%Y-%m-31',t.date)
order by t2.date desc limit 1) AS LAST_FOO
from MyTable t
GROUP BY YEAR_MONTH
ORDER BY YEAR_MONTH;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
