Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Bill McCormick
Igor Tandetnik wrote, On 2/2/2012 10:50 AM: Petite Abeille 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

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Igor Tandetnik
Petite Abeille 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: >

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Petite Abeille
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:

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Igor Tandetnik
Bill McCormick 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. >

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Bill McCormick
Mr. Puneet Kishor wrote, On 2/2/2012 9:57 AM: On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote: On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: Does SQLite have FIRST and LAST aggregate function? No, sadly, SQLite doesn't support any analytic functions (aka window function) such as

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Mr. Puneet Kishor
On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote: > > On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: > >> Does SQLite have FIRST and LAST aggregate function? > > No, sadly, SQLite doesn't support any analytic functions (aka window > function) such as first, last, lead, lag, rank, etc,

Re: [sqlite] FIRST/LAST function

2012-02-02 Thread Petite Abeille
On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: > Does SQLite have FIRST and LAST aggregate function? No, sadly, SQLite doesn't support any analytic functions (aka window function) such as first, last, lead, lag, rank, etc, etc... [1] To achieve the same, you will have to roll your own,

[sqlite] FIRST/LAST function

2012-02-02 Thread Bill McCormick
Does SQLite have FIRST and LAST aggregate function? I don't see it in the docs, but I thought I might ask anyway. Or maybe someone out there knows a slick way to do this: Anyway, it would be sort of like MIN & MAX, but would return the first and last values of the requested field over a