Re: [sqlite] Window functions

On 5/9/62 00:13, David Raymond wrote: Kind of annoying that when the author shows a screenshot of the sample data he's using for his queries that he doesn't include 2 of the fields that are in the queries. Makes it harder to "play along at home" For their ntile example (on page2) I don't thin

Re: [sqlite] Window functions

Kind of annoying that when the author shows a screenshot of the sample data he's using for his queries that he doesn't include 2 of the fields that are in the queries. Makes it harder to "play along at home" For their ntile example (on page2) I don't think I've seen a window function used with

Re: [sqlite] Window functions

On 4/9/62 23:14, Simon Slavin wrote: I ran into this two-part article, probably on Hacker News: I tried comparing it with but I don't know enough to be able to tell whether the languag

Re: [sqlite] Window functions in sqlite 3.26.0

ember, 2018 12:24 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Window functions in sqlite 3.26.0 > >Thank you, Dan. My eyes saw the underscore, but my brain did not >process >it! > >And many thanks to the SQLite development team for introducing this >

Re: [sqlite] Window functions in sqlite 3.26.0

2018 14:40:58 +0700 > Subject: Re: [sqlite] Window functions in sqlite 3.26.0 > On 12/24/2018 01:43 AM, Balaji Ramanathan wrote: > > Hi, > > > > Are window functions enabled by default in the sqlite command line > > shell program that is available for download on

Re: [sqlite] Window functions in sqlite 3.26.0

On 12/24/2018 01:43 AM, Balaji Ramanathan wrote: Hi, Are window functions enabled by default in the sqlite command line shell program that is available for download on the sqlite website? I get the error message "no such function: rownumber()" when I try to use that window function. They

Re: [sqlite] Window functions?

I see the point being made by many in this thread. I am not against expanding SQLite's functionality. But, if I might, I will throw out some contrarian ideas. First is that SQLite really is an embedded SQL data base. It is meant to be combined into your application's main executable file. So the la

Re: [sqlite] Window functions?

In 2008 I was part of a project which was mostly DB driven using Sybase 12.5. Sybase neither had support for user functions nor window functions & many other features, which other contemporary RDBMS had and same arguments was thrown why you need user functions or window functions if both can be ac

Re: [sqlite] Window functions

> On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer wrote: > >> Hi, >> >> I'm sure this has been asked before, but are window functions on the >> roadmap? Is it the authors' experience that the implementation would >> significantly complicate sqlite? Just curious. Thanks so much for a >> fantasti

Re: [sqlite] Window functions

What are 'window functions'? If you mean GUI; that's really outside the scope of Sqlite; and whatever environment you're in can provide your GUI; https://www.npmjs.com/package/sack-gui for instance for Javascript(Node.js). On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer wrote: > Hi, > > I'm su

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 18:25:28 -0600 "Keith Medcalf" wrote: > >> select id, category_id, name, min(price) as minprice > >>from cat_pictures > >> group by category_id; > > >This peculiar behavior is very unique to SQLite. > > Not really. Sybase, SQL Server and DB2 do (or did do) the same thi

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 23:04:40 +0200 Petite Abeille wrote: > > On Aug 27, 2014, at 10:57 PM, Eduardo Morras > wrote: > > > Sorry, don't understand why others will throw an exception in the > > group by, perhaps I'm misunderstanding the group by, but that > > should work on others engines. > > B

Re: [sqlite] Window functions?

On 28 Aug 2014, at 3:45pm, Richard Hipp wrote: > On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck < > ggrothendi...@gmail.com> wrote: > >> The wording in the cited link is that >> >> "Queries of the form: "SELECT max(x), y FROM table" returns the value >> of y on the same row that contains

Re: [sqlite] Window functions?

On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck < ggrothendi...@gmail.com> wrote: > The wording in the cited link is that > > "Queries of the form: "SELECT max(x), y FROM table" returns the value > of y on the same row that contains the maximum x value." > > There is some question of whether

Re: [sqlite] Window functions?

The wording in the cited link is that "Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value." There is some question of whether min(x) is "of the form" max(x). On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch wrote: > Adam

Re: [sqlite] Window functions?

Adam Devita wrote: >>> select id, category_id, name, min(price) as minprice >>>from cat_pictures >>> group by category_id; >>> > > I'd be reluctant to write that query because it is non standard SQL and I > can't easily (5 minutes of searching) point at a document that tells me the > expected b

Re: [sqlite] Window functions?

dbase3 would give an error if you did not include all the non-aggregate fields in the Group By. (One could also step forward/backward in a row-set, so some crude windowing was available if one coded to do that.) on this: > > select id, category_id, name, min(price) as minprice > >from cat_pic

Re: [sqlite] Window functions?

1, Line 3 Column 'cat_pictures.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. -Original Message- From: Keith Medcalf To: General Discussion of SQLite Database Sent: Wed, Aug 27, 2014 5:25 pm Subject:

Re: [sqlite] Window functions?

On Wednesday, 27 August, 2014 13:17, Petite Abeille said: >On Aug 26, 2014, at 2:09 AM, Keith Medcalf wrote: >> select id, category_id, name, min(price) as minprice >>from cat_pictures >> group by category_id; >> >> Done. And no need for any windowing functions ... >This peculiar behavio

Re: [sqlite] Window functions?

above works, it is a SQLite "feature" or mis-feature. Regards. -Original Message- From: Petite Abeille To: General Discussion of SQLite Database Sent: Wed, Aug 27, 2014 2:04 pm Subject: Re: [sqlite] Window functions? On Aug 27, 2014, at 10:57 PM, Eduardo Morras wrote:

Re: [sqlite] Window functions?

On Aug 27, 2014, at 10:57 PM, Eduardo Morras wrote: > Sorry, don't understand why others will throw an exception in the group by, > perhaps I'm misunderstanding the group by, but that should work on others > engines. Because not all expressions are accounted for, i.e.: "not a GROUP BY expres

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 21:17:05 +0200 Petite Abeille wrote: > > On Aug 26, 2014, at 2:09 AM, Keith Medcalf > wrote: > > > select id, category_id, name, min(price) as minprice > >from cat_pictures > > group by category_id; > > > > Done. And no need for any windowing functions … > > This pe

Re: [sqlite] Window functions?

On Aug 26, 2014, at 2:09 AM, Keith Medcalf wrote: > select id, category_id, name, min(price) as minprice >from cat_pictures > group by category_id; > > Done. And no need for any windowing functions … This peculiar behavior is very unique to SQLite. Most reasonable SQL engines will throw

Re: [sqlite] Window functions?

On 2014-08-26 18:00, sqlite-users-requ...@sqlite.org wrote: SELECT employee_name, employee_id, salary, rank() OVER(PARTITION BY dept ORDER BY salary DESC), 100.0*salary/sum(salary) OVER (PARTITION BY dept) FROM employee; I don't know if the above is valid SQL or not. But is seems

Re: [sqlite] Window functions?

On 25.08.2014 20:47, Richard Hipp wrote: On Mon, Aug 25, 2014 at 1:21 PM, forkandwait wrote: You used the word "immense" which I like - it is an apt description of the knowledge and effort needed to add windowing functions to SQLite (and probably any other database engine for that matter).

Re: [sqlite] Window functions?

On Monday, August 25, 2014 09:25:47 PM Stephan Beal wrote: > On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille > > wrote: > > True. But what a quantum leap that would be. Like moving from the > > wheelbarrow to the jet engine. > > For the small percentage of users who need it (or would even know h

Re: [sqlite] Window functions?

ait >Sent: Monday, 25 August, 2014 11:19 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Window functions? > >Simon Slavin writes: > > >> Would you care to explain what advantages Window functions would give >us >that VIEWs and sub-SELECTs don't >> give

Re: [sqlite] Window functions?

Hi Stephan, "lite," is not a mathematic definition, and is increasing over time. (at least 5% per year in Sqlite code size, by 30% in smartphone capabilities ) ==> What was "heavy" in 2003 will become "lite" one day. ___ sqlite-users mailing list sqlite

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 9:43 PM, forkandwait wrote: > Stephan Beal writes: > > > For the small percentage of users who need it (or would even know how to > > apply it). i've been following this list since 2006 or 2007 and i recall > > this topic having come up only a small handful of times, whic

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 4:20 PM, forkandwait wrote: > > I would be interested to hear what parts of the full window function spec > are not covered by the example, if someone can describe it easily. > SELECT employee_name, employee_id, salary, rank() OVER(PARTITION BY dept ORDER BY sala

Re: [sqlite] Window functions?

On Aug 25, 2014, at 10:20 PM, forkandwait wrote: > I would be interested to hear what parts of the full window function spec > are not covered by the example, if someone can describe it easily. Well, the exact implementation varies from implementation to implementation, e.g. Oracle sports more

Re: [sqlite] Window functions?

big stone writes: > - but would a basic subset, like the one described here in March '14 ( > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-March/051635.html For non-subscribers to read: http://thread.gmane.org/gmane.comp.db.sqlite.general/86702 > ) : > . be a sufficien

Re: [sqlite] Window functions?

Hi, For one of the few wishing it : - I can understand when Richard writes it's very complex to implement "in full", as I can imagine tricky requests with it, - but would a basic subset, like the one described here in March '14 ( http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-M

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:18 PM, forkandwait wrote: > Compare the two SQL examples between Approach 2 and Approach 3 in the linked > page: > > http://hashrocket.com/blog/posts/sql-window-functions Couple more: There was SQL before window functions and SQL after window functions http://tapoueh.org

Re: [sqlite] Window functions?

Stephan Beal writes: > For the small percentage of users who need it (or would even know how to > apply it). i've been following this list since 2006 or 2007 and i recall > this topic having come up only a small handful of times, which implies that > only a small minority of users feels the need

Re: [sqlite] Window functions?

Stephan Beal wrote on Monday, August 25, 2014 3:26 PM > > For the small percentage of users who need it (or would even know how > to apply it). i've been following this list since 2006 or 2007 and i > recall this topic having come up only a small handful of times, which > implies that only a small

Re: [sqlite] Window functions?

On Aug 25, 2014, at 9:25 PM, Stephan Beal wrote: > For the small percentage of users who need it (or would even know how to > apply it). i've been following this list since 2006 or 2007 and i recall > this topic having come up only a small handful of times, which implies that > only a small mino

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille wrote: > True. But what a quantum leap that would be. Like moving from the > wheelbarrow to the jet engine. > For the small percentage of users who need it (or would even know how to apply it). i've been following this list since 2006 or 2007 and i

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:04 PM, Simon Slavin wrote: > Would you care to explain what advantages Window functions would give us that > VIEWs and sub-SELECTs don't give us ? I'm not being contrary, I'd like to > know. Analytics are to sub-selects like cruise missile are to muskets: an entirely d

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:12 PM, Richard Hipp wrote: > You used the word "immense" which I like - it is an apt description of the > knowledge and effort needed to add windowing functions to SQLite (and > probably any other database engine for that matter). True. But what a quantum leap that would b

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 1:21 PM, forkandwait wrote: > > You used the word "immense" which I like - it is an apt description of > the > > knowledge and effort needed to add windowing functions to SQLite (and > > probably any other database engine for that matter). > > Hehe. I would be interested

Re: [sqlite] Window functions?

> You used the word "immense" which I like - it is an apt description of the > knowledge and effort needed to add windowing functions to SQLite (and > probably any other database engine for that matter). Hehe. I would be interested in any of your specific thoughts on the immensity of it. I can i

Re: [sqlite] Window functions?

Simon Slavin writes: > Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't > give us ? I'm not being contrary, I'd like to know. I have never compared lines of code between the various approaches, but window functions make it pretty simple

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 11:43 AM, forkandwait wrote: > > I am not promising anything, but I would be interested in a sketch of it > might take a hacker to add these to SQLite -- what files need to be > touched, > what sections of the lemon parser, etc. > You used the word "immense" which I like

Re: [sqlite] Window functions?

On 25 Aug 2014, at 4:43pm, forkandwait wrote: > Has anyone thought in some detail about what it would it take to add window > functions to SQLite? Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't give us ? I'm not being contrary, I'd li

Re: [sqlite] Window functions

I'm using sqlite's count function to emulate OLAP functionality. Unfortunately count function is not exposed in sqlite, nevertheless it eases the pain of not having analytics functions in sqlite (lead, lag, median etc). lefteris Alexey Pechnikov wrote: > Hello! > > В сообщении от Monday 24 N

Re: [sqlite] Window functions

Hello! В сообщении от Monday 24 November 2008 19:16:46 Constantine Vassil написал(а): > OLAP functionality includes the concept of a sliding *window* that moves > down > through the input rows as they are processed. Additional calculations can > be > > performed on the data in the window as it mov