On Wed, 27 Aug 2014 18:25:28 -0600
Keith Medcalf kmedc...@dessus.com 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
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
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 behavior.
The
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 clem...@ladisch.de
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 min(x) is
On 28 Aug 2014, at 3:45pm, Richard Hipp d...@sqlite.org 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
On Wed, 27 Aug 2014 23:04:40 +0200
Petite Abeille petite.abei...@gmail.com wrote:
On Aug 27, 2014, at 10:57 PM, Eduardo Morras emorr...@yahoo.es
wrote:
Sorry, don't understand why others will throw an exception in the
group by, perhaps I'm misunderstanding the group by, but that
On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com 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
On Wed, 27 Aug 2014 21:17:05 +0200
Petite Abeille petite.abei...@gmail.com wrote:
On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com
wrote:
select id, category_id, name, min(price) as minprice
from cat_pictures
group by category_id;
Done. And no need for any
On Aug 27, 2014, at 10:57 PM, Eduardo Morras emorr...@yahoo.es 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
, it is a SQLite feature or
mis-feature.
Regards.
-Original Message-
From: Petite Abeille petite.abei...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Wed, Aug 27, 2014 2:04 pm
Subject: Re: [sqlite] Window functions?
On Aug 27, 2014, at 10:57 PM, Eduardo Morras
On Wednesday, 27 August, 2014 13:17, Petite Abeille said:
On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com 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
: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 kmedc...@dessus.com wrote:
select id, category_id, name, min(price) as minprice
from cat_pictures
group by category_id;
Done. And no need
On Monday, August 25, 2014 09:25:47 PM Stephan Beal wrote:
On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille petite.abei...@gmail.com
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
On 25.08.2014 20:47, Richard Hipp wrote:
On Mon, Aug 25, 2014 at 1:21 PM, forkandwait webb.spra...@gmail.com 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
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
Has anyone thought in some detail about what it would it take to add window
functions to SQLite?
http://www.postgresql.org/docs/9.4/static/tutorial-window.html
For data analysis shops like us (think SAS + baroquely complex Excel + lots
of graphs), SQLite with window functions would be immense.
On 25 Aug 2014, at 4:43pm, forkandwait webb.spra...@gmail.com 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
On Mon, Aug 25, 2014 at 11:43 AM, forkandwait webb.spra...@gmail.com
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
Simon Slavin slavins@... 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
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
On Mon, Aug 25, 2014 at 1:21 PM, forkandwait webb.spra...@gmail.com 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
On Aug 25, 2014, at 7:12 PM, Richard Hipp d...@sqlite.org 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
On Aug 25, 2014, at 7:04 PM, Simon Slavin slav...@bigfraud.org 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
On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille petite.abei...@gmail.com
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
On Aug 25, 2014, at 9:25 PM, Stephan Beal sgb...@googlemail.com 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
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
Stephan Beal sgbeal@... 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
On Aug 25, 2014, at 7:18 PM, forkandwait webb.spra...@gmail.com 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
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 (
big stone stonebig34@... 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
On Aug 25, 2014, at 10:20 PM, forkandwait webb.spra...@gmail.com 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,
On Mon, Aug 25, 2014 at 4:20 PM, forkandwait webb.spra...@gmail.com 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
On Mon, Aug 25, 2014 at 9:43 PM, forkandwait webb.spra...@gmail.com wrote:
Stephan Beal sgbeal@... 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
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
, 25 August, 2014 11:19
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Window functions?
Simon Slavin slavins@... 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
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
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 moves,
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 moves, allowing further analysis
in a
manner that is more efficient than using semantically
39 matches
Mail list logo
