Question about hidden columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Question about hidden columns

Brandon Kyle Schmoll
Hello All,
   I am relatively new to sqlite and I have a question about using input parameters to constrain hidden columns in a query, described in section 2.1.2 here:

https://www.sqlite.org/vtab.html#table_valued_functions <https://www.sqlite.org/vtab.html#table_valued_functions>

The documentation states that one can specify the constraint parameters as in a function call where the first input to the function will constrain the first hidden column, the second input will constrain the second hidden column, and so forth.  In other words, this method emulates a function call using positional arguments.  However, it would be useful to use keyword arguments in case you want to constrain some, but not all, of the hidden columns.  Is this possible?

Thanks,
Brandon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

LIMIT

Gert Van Assche-2
 All,

I'm sure it must be possible, I just don't find how.
I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
are not unique.
I would like to get only 10 F1 fields for each unique F2.

LIMIT 10 is the easy part... But how do I express the query so it F1 is
LIMITed to 10 for every F2?

I hope I explained well.

Thanks

> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

R Smith-2

On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>   All,
>
> I'm sure it must be possible, I just don't find how.
> I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
> are not unique.
> I would like to get only 10 F1 fields for each unique F2.

This is not normally done, and windowing functions in other RDBMSes
makes for an easier way, but it can still be done in SQLite with some
creative grouping of a self-joined query.
In this example, I limited it to 3 F1 items per unique F2 for brevity,
but you can easily change the "< 4" to "< 11" or "<= 10" according to
preference.

   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
version 2.0.2.4.
   --
================================================================================================

CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);

INSERT INTO T(F1,F2) VALUES
  (1,  'John')
,(2,  'John')
,(3,  'Jason')
,(4,  'John')
,(5,  'Jason')
,(6,  'John')
,(7,  'John')
,(8,  'Jason')
,(9,  'Jason')
,(10,  'Joan')
,(11,  'Joan')
,(12,  'Joan')
,(13,  'Jimmy')
;

SELECT A.F2, B.F1
   FROM T AS A
   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
  GROUP BY A.F2, B.F1
  HAVING COUNT(*) < 4
;

   -- F2    |  F1
   -- ----- | ---
   -- Jason |  3
   -- Jason |  5
   -- Jason |  8
   -- Jimmy |  13
   -- Joan  |  10
   -- Joan  |  11
   -- Joan  |  12
   -- John  |  1
   -- John  |  2
   -- John  |  4



-- Another option to note, in case the 10 limit is not important and
simply aimed
-- at saving space, is to use group concatenation, like so:

SELECT F2, group_concat(F1)AS F1
   FROM T
  GROUP BY F2
;

   --       |
   -- F2    |    F1
   -- ----- | ---------
   -- Jason |  3,5,8,9
   -- Jimmy |     13
   -- Joan  |  10,11,12
   -- John  | 1,2,4,6,7


   --
------------------------------------------------------------------------------------------------

Cheers!
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Dan Kennedy-4
On 06/23/2018 03:52 AM, R Smith wrote:

>
> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>>   All,
>>
>> I'm sure it must be possible, I just don't find how.
>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
>> are not unique.
>> I would like to get only 10 F1 fields for each unique F2.
>
> This is not normally done, and windowing functions in other RDBMSes
> makes for an easier way, but it can still be done in SQLite with some
> creative grouping of a self-joined query.
> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> preference.

What would the window-function query be?

Dan.



>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>   --
> ================================================================================================
>
> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>
> INSERT INTO T(F1,F2) VALUES
>  (1,  'John')
> ,(2,  'John')
> ,(3,  'Jason')
> ,(4,  'John')
> ,(5,  'Jason')
> ,(6,  'John')
> ,(7,  'John')
> ,(8,  'Jason')
> ,(9,  'Jason')
> ,(10,  'Joan')
> ,(11,  'Joan')
> ,(12,  'Joan')
> ,(13,  'Jimmy')
> ;
>
> SELECT A.F2, B.F1
>   FROM T AS A
>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>  GROUP BY A.F2, B.F1
>  HAVING COUNT(*) < 4
> ;
>
>   -- F2    |  F1
>   -- ----- | ---
>   -- Jason |  3
>   -- Jason |  5
>   -- Jason |  8
>   -- Jimmy |  13
>   -- Joan  |  10
>   -- Joan  |  11
>   -- Joan  |  12
>   -- John  |  1
>   -- John  |  2
>   -- John  |  4
>
>
>
> -- Another option to note, in case the 10 limit is not important and
> simply aimed
> -- at saving space, is to use group concatenation, like so:
>
> SELECT F2, group_concat(F1)AS F1
>   FROM T
>  GROUP BY F2
> ;
>
>   --       |
>   -- F2    |    F1
>   -- ----- | ---------
>   -- Jason |  3,5,8,9
>   -- Jimmy |     13
>   -- Joan  |  10,11,12
>   -- John  | 1,2,4,6,7
>
>
>   --
> ------------------------------------------------------------------------------------------------
>
> Cheers!
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

R Smith


On 2018/06/22 11:05 PM, Dan Kennedy wrote:

> On 06/23/2018 03:52 AM, R Smith wrote:
>>
>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>>>   All,
>>>
>>> I'm sure it must be possible, I just don't find how.
>>> I have a table T with 2 fields (F1 and F2). The F1 are unique and
>>> the F2
>>> are not unique.
>>> I would like to get only 10 F1 fields for each unique F2.
>>
>> This is not normally done, and windowing functions in other RDBMSes
>> makes for an easier way, but it can still be done in SQLite with some
>> creative grouping of a self-joined query.
>> In this example, I limited it to 3 F1 items per unique F2 for
>> brevity, but you can easily change the "< 4" to "< 11" or "<= 10"
>> according to preference.
>
> What would the window-function query be?

Mainly one can avoid the self-join with a windowing function, like this:

SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY
F1) AS C, F2, F1) AS XWHERE X.C <= 10

or

SELECT F2, F1
   FROM (
      SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1
DESC) AS R
   ) AS X
WHERE R <= 10



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about hidden columns

Keith Medcalf
In reply to this post by Brandon Kyle Schmoll

You know that you can use the hidden columns by name in the WHERE cause correct, and do not have to use function parameters?

So the ext/misc/series.c in the repository defines a virtual table generate_series which effectively returns each "value" generated by the statement:

for (value=start; value+=step; value<=stop) generate_the_value(value);

You can use it as either select value from generate_series(1,100,2); the hidden columns are start, stop, step.  Equivalently you can do
select value from generate_series where start=1 and stop=100 and step=2;

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Brandon Kyle Schmoll
>Sent: Friday, 22 June, 2018 12:30
>To: [hidden email]
>Subject: [sqlite] Question about hidden columns
>
>Hello All,
>   I am relatively new to sqlite and I have a question about using
>input parameters to constrain hidden columns in a query, described in
>section 2.1.2 here:
>
>https://www.sqlite.org/vtab.html#table_valued_functions
><https://www.sqlite.org/vtab.html#table_valued_functions>
>
>The documentation states that one can specify the constraint
>parameters as in a function call where the first input to the
>function will constrain the first hidden column, the second input
>will constrain the second hidden column, and so forth.  In other
>words, this method emulates a function call using positional
>arguments.  However, it would be useful to use keyword arguments in
>case you want to constrain some, but not all, of the hidden columns.
>Is this possible?
>
>Thanks,
>Brandon
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

sub sk79
In reply to this post by Dan Kennedy-4
> What would the window-function query be?


Note: For anyone stumbling upon this thread, below code is not supported in
SQLite natively.

SELECT F1, F2

    FROM (

        SELECT F2, F1, Rank()

          OVER (PARTITION BY F2

                ORDER BY F1 ) AS Rank

        FROM T

        )  WHERE Rank <= 10

>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
In reply to this post by R Smith-2
Hi Ryan, thanks for this. This is working if the F1 field is a numeric
value. With text (especially Asian & Arabic characters) this does not seem
to work.
So I created an MD5 hash from the text fields and it works great! Thank you
so much.

gert

Op vr 22 jun. 2018 om 22:52 schreef R Smith <[hidden email]>:

>
> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >   All,
> >
> > I'm sure it must be possible, I just don't find how.
> > I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
> > are not unique.
> > I would like to get only 10 F1 fields for each unique F2.
>
> This is not normally done, and windowing functions in other RDBMSes
> makes for an easier way, but it can still be done in SQLite with some
> creative grouping of a self-joined query.
> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> preference.
>
>    -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>    --
>
> ================================================================================================
>
> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>
> INSERT INTO T(F1,F2) VALUES
>   (1,  'John')
> ,(2,  'John')
> ,(3,  'Jason')
> ,(4,  'John')
> ,(5,  'Jason')
> ,(6,  'John')
> ,(7,  'John')
> ,(8,  'Jason')
> ,(9,  'Jason')
> ,(10,  'Joan')
> ,(11,  'Joan')
> ,(12,  'Joan')
> ,(13,  'Jimmy')
> ;
>
> SELECT A.F2, B.F1
>    FROM T AS A
>    JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>   GROUP BY A.F2, B.F1
>   HAVING COUNT(*) < 4
> ;
>
>    -- F2    |  F1
>    -- ----- | ---
>    -- Jason |  3
>    -- Jason |  5
>    -- Jason |  8
>    -- Jimmy |  13
>    -- Joan  |  10
>    -- Joan  |  11
>    -- Joan  |  12
>    -- John  |  1
>    -- John  |  2
>    -- John  |  4
>
>
>
> -- Another option to note, in case the 10 limit is not important and
> simply aimed
> -- at saving space, is to use group concatenation, like so:
>
> SELECT F2, group_concat(F1)AS F1
>    FROM T
>   GROUP BY F2
> ;
>
>    --       |
>    -- F2    |    F1
>    -- ----- | ---------
>    -- Jason |  3,5,8,9
>    -- Jimmy |     13
>    -- Joan  |  10,11,12
>    -- John  | 1,2,4,6,7
>
>
>    --
>
> ------------------------------------------------------------------------------------------------
>
> Cheers!
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Barry Smith
Ryan's way works well. Here is a second method which expresses it in a different way:

SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2 ORDER BY rowid LIMIT 10)

If you have WITHOUT ROWID tables you'd have to replace rowid with your primary key.

(The query may still work without the ORDER BY, I didn't test it, but even if it does a future query optimiser might break that because without the order by the results of the inner select are free to include a different 10 rowids for every value in the outer query)

> On 23 Jun 2018, at 9:50 pm, Gert Van Assche <[hidden email]> wrote:
>
> Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> value. With text (especially Asian & Arabic characters) this does not seem
> to work.
> So I created an MD5 hash from the text fields and it works great! Thank you
> so much.
>
> gert
>
> Op vr 22 jun. 2018 om 22:52 schreef R Smith <[hidden email]>:
>
>>
>>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>>>  All,
>>>
>>> I'm sure it must be possible, I just don't find how.
>>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
>>> are not unique.
>>> I would like to get only 10 F1 fields for each unique F2.
>>
>> This is not normally done, and windowing functions in other RDBMSes
>> makes for an easier way, but it can still be done in SQLite with some
>> creative grouping of a self-joined query.
>> In this example, I limited it to 3 F1 items per unique F2 for brevity,
>> but you can easily change the "< 4" to "< 11" or "<= 10" according to
>> preference.
>>
>>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
>> version 2.0.2.4.
>>   --
>>
>> ================================================================================================
>>
>> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>>
>> INSERT INTO T(F1,F2) VALUES
>>  (1,  'John')
>> ,(2,  'John')
>> ,(3,  'Jason')
>> ,(4,  'John')
>> ,(5,  'Jason')
>> ,(6,  'John')
>> ,(7,  'John')
>> ,(8,  'Jason')
>> ,(9,  'Jason')
>> ,(10,  'Joan')
>> ,(11,  'Joan')
>> ,(12,  'Joan')
>> ,(13,  'Jimmy')
>> ;
>>
>> SELECT A.F2, B.F1
>>   FROM T AS A
>>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>>  GROUP BY A.F2, B.F1
>>  HAVING COUNT(*) < 4
>> ;
>>
>>   -- F2    |  F1
>>   -- ----- | ---
>>   -- Jason |  3
>>   -- Jason |  5
>>   -- Jason |  8
>>   -- Jimmy |  13
>>   -- Joan  |  10
>>   -- Joan  |  11
>>   -- Joan  |  12
>>   -- John  |  1
>>   -- John  |  2
>>   -- John  |  4
>>
>>
>>
>> -- Another option to note, in case the 10 limit is not important and
>> simply aimed
>> -- at saving space, is to use group concatenation, like so:
>>
>> SELECT F2, group_concat(F1)AS F1
>>   FROM T
>>  GROUP BY F2
>> ;
>>
>>   --       |
>>   -- F2    |    F1
>>   -- ----- | ---------
>>   -- Jason |  3,5,8,9
>>   -- Jimmy |     13
>>   -- Joan  |  10,11,12
>>   -- John  | 1,2,4,6,7
>>
>>
>>   --
>>
>> ------------------------------------------------------------------------------------------------
>>
>> Cheers!
>> Ryan
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.

gert

Op za 23 jun. 2018 om 14:32 schreef Barry Smith <[hidden email]>:

> Ryan's way works well. Here is a second method which expresses it in a
> different way:
>
> SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 =
> t2.F2 ORDER BY rowid LIMIT 10)
>
> If you have WITHOUT ROWID tables you'd have to replace rowid with your
> primary key.
>
> (The query may still work without the ORDER BY, I didn't test it, but even
> if it does a future query optimiser might break that because without the
> order by the results of the inner select are free to include a different 10
> rowids for every value in the outer query)
>
> > On 23 Jun 2018, at 9:50 pm, Gert Van Assche <[hidden email]> wrote:
> >
> > Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> > value. With text (especially Asian & Arabic characters) this does not
> seem
> > to work.
> > So I created an MD5 hash from the text fields and it works great! Thank
> you
> > so much.
> >
> > gert
> >
> > Op vr 22 jun. 2018 om 22:52 schreef R Smith <[hidden email]>:
> >
> >>
> >>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>  All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the
> F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> >> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> >> preference.
> >>
> >>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> >> version 2.0.2.4.
> >>   --
> >>
> >>
> ================================================================================================
> >>
> >> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
> >>
> >> INSERT INTO T(F1,F2) VALUES
> >>  (1,  'John')
> >> ,(2,  'John')
> >> ,(3,  'Jason')
> >> ,(4,  'John')
> >> ,(5,  'Jason')
> >> ,(6,  'John')
> >> ,(7,  'John')
> >> ,(8,  'Jason')
> >> ,(9,  'Jason')
> >> ,(10,  'Joan')
> >> ,(11,  'Joan')
> >> ,(12,  'Joan')
> >> ,(13,  'Jimmy')
> >> ;
> >>
> >> SELECT A.F2, B.F1
> >>   FROM T AS A
> >>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
> >>  GROUP BY A.F2, B.F1
> >>  HAVING COUNT(*) < 4
> >> ;
> >>
> >>   -- F2    |  F1
> >>   -- ----- | ---
> >>   -- Jason |  3
> >>   -- Jason |  5
> >>   -- Jason |  8
> >>   -- Jimmy |  13
> >>   -- Joan  |  10
> >>   -- Joan  |  11
> >>   -- Joan  |  12
> >>   -- John  |  1
> >>   -- John  |  2
> >>   -- John  |  4
> >>
> >>
> >>
> >> -- Another option to note, in case the 10 limit is not important and
> >> simply aimed
> >> -- at saving space, is to use group concatenation, like so:
> >>
> >> SELECT F2, group_concat(F1)AS F1
> >>   FROM T
> >>  GROUP BY F2
> >> ;
> >>
> >>   --       |
> >>   -- F2    |    F1
> >>   -- ----- | ---------
> >>   -- Jason |  3,5,8,9
> >>   -- Jimmy |     13
> >>   -- Joan  |  10,11,12
> >>   -- John  | 1,2,4,6,7
> >>
> >>
> >>   --
> >>
> >>
> ------------------------------------------------------------------------------------------------
> >>
> >> Cheers!
> >> Ryan
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

R Smith-2

On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> Barry, that's even easier indeed. And it works perfect!
> Thanks for sharing this.

I very much like the method which Barry suggested for the simplicity,
but have avoided it since I thought it would be significantly slower on
a large data set, however, SQLite is known for some pretty neat
optimizations (especially where the row_id is concerned) and I'm now
interested to know if indeed such optimizations feature in this case. I
have a few such implemented queries that might benefit from changing over.

May I ask, if you do have a rather large dataset, and perhaps don't mind
the time, would you care to compare the two methods and let us know if
there is any discernible difference in speed? Also one small important
item - how many F1 items are there typically per unique F2 item in
total? Does it grow over time?

Also, you do not need hashes, for either of the two queries - the
queries should work regardless of integer or Unicode text used, perhaps
the comparison might need a specific collation? Surrounding both
references with a TRIM() function might also help. Essentially, if F1 of
record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug. 
The <, <=, >, >= might all produce some Unicode weirdness upon
inadequate collations.


If time doesn't allow, then don't spend effort on this, it's simply a
curiosity. :)

Thanks!
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Simon Slavin-3
On 23 Jun 2018, at 5:08pm, R Smith <[hidden email]> wrote:

> May I ask, if you do have a rather large dataset, and perhaps don't mind the time, would you care to compare the two methods and let us know if there is any discernible difference in speed?

Answers will be different depending on type of main storage, size of cache available, and how much of the computer's processors are devoted to SQLite.  I used to have an overnight run (not using SQLite) on an early dual-processor machine which took three times longer to work if a tiny clock was showing on the display.  The clock hogged one of the processors.

It would be an interesting comparison, but it's valid only for the setup it's run on.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
In reply to this post by R Smith-2
Ryan, my dataset isn't that big. 11K records.
Your solution is noticeable faster 996 ms vs 13126 ms.
Interesting!

gert

Op za 23 jun. 2018 om 18:09 schreef R Smith <[hidden email]>:

>
> On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > Barry, that's even easier indeed. And it works perfect!
> > Thanks for sharing this.
>
> I very much like the method which Barry suggested for the simplicity,
> but have avoided it since I thought it would be significantly slower on
> a large data set, however, SQLite is known for some pretty neat
> optimizations (especially where the row_id is concerned) and I'm now
> interested to know if indeed such optimizations feature in this case. I
> have a few such implemented queries that might benefit from changing over.
>
> May I ask, if you do have a rather large dataset, and perhaps don't mind
> the time, would you care to compare the two methods and let us know if
> there is any discernible difference in speed? Also one small important
> item - how many F1 items are there typically per unique F2 item in
> total? Does it grow over time?
>
> Also, you do not need hashes, for either of the two queries - the
> queries should work regardless of integer or Unicode text used, perhaps
> the comparison might need a specific collation? Surrounding both
> references with a TRIM() function might also help. Essentially, if F1 of
> record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
> WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.
> The <, <=, >, >= might all produce some Unicode weirdness upon
> inadequate collations.
>
>
> If time doesn't allow, then don't spend effort on this, it's simply a
> curiosity. :)
>
> Thanks!
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Barry Smith
I checked with a fake dataset: the GROUP BY solution is quicker with no
index on F2 (the query planner can understand that query well enough to
create an automatic covering index). However, with an index on F2, the
rowid solution is considerably faster for a dataset with 2600 random
distinct F2 and one million total rows in T.

The test script is at the end of the email. On my computer, with the rowid
correlation, the query returned in 14 seconds*. With the GROUP BY solution,
the query took 50 seconds.

It was interesting to see that both queries ran quickly spitting out values
until SQLite had returned all possible values, then stalled for a long time
as the DB engine processed all rows that could never return anything.

Cheers,

 - Barry

[*I suspected that most of the time with the rowid query was taken in
writing to the console, so I SELECT'd COUNT(*) instead of * and it returned
in 6 seconds, and I suspect still did most of the work (the query plan was
the same). I tried the same thing on the GROUP BY query, but the GROUP BY
and COUNT don't play so well together and it still spat out a bunch of
numbers to the console.]

CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT NOT NULL);

CREATE TABLE Names(id INTEGER PRIMARY KEY, Name TEXT NOT NULL);

INSERT INTO Names(Name) VALUES
('Alex'),('Brett'),('Cindy'),('Dennis'),('Echo'),('Frank'),('Garry'),('Herbert'),('Indigo'),('Jack'),('Karl'),('Lima'),('Melon'),('Nunes'),('Oprah'),('Peter'),('Quincy'),('Robert'),('Sarah'),('Tangerine'),('Unicorn'),('Violet'),('Wilfred'),('Violet'),('Wesley'),('Xavier'),('Yeltzin'),('Zbrudov');

WITH num(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM num WHERE i < 100)
INSERT INTO Names(Name)
SELECT Name || i FROM Names, Num;

WITH num(i, rnd) AS (SELECT 1, RANDOM()%1300 + 1300 UNION ALL SELECT i + 1,
RANDOM()%1300 + 1300 FROM num WHERE i < 1000000)
INSERT INTO T(F2)
SELECT (SELECT Name FROM Names WHERE id = rnd) FROM num;

CREATE INDEX idx_F2 ON T(F2);

On Sun, 24 Jun 2018 at 07:00, Gert Van Assche <[hidden email]> wrote:

> Ryan, my dataset isn't that big. 11K records.
> Your solution is noticeable faster 996 ms vs 13126 ms.
> Interesting!
>
> gert
>
> Op za 23 jun. 2018 om 18:09 schreef R Smith <[hidden email]>:
>
> >
> > On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > > Barry, that's even easier indeed. And it works perfect!
> > > Thanks for sharing this.
> >
> > I very much like the method which Barry suggested for the simplicity,
> > but have avoided it since I thought it would be significantly slower on
> > a large data set, however, SQLite is known for some pretty neat
> > optimizations (especially where the row_id is concerned) and I'm now
> > interested to know if indeed such optimizations feature in this case. I
> > have a few such implemented queries that might benefit from changing
> over.
> >
> > May I ask, if you do have a rather large dataset, and perhaps don't mind
> > the time, would you care to compare the two methods and let us know if
> > there is any discernible difference in speed? Also one small important
> > item - how many F1 items are there typically per unique F2 item in
> > total? Does it grow over time?
> >
> > Also, you do not need hashes, for either of the two queries - the
> > queries should work regardless of integer or Unicode text used, perhaps
> > the comparison might need a specific collation? Surrounding both
> > references with a TRIM() function might also help. Essentially, if F1 of
> > record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
> > WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.
> > The <, <=, >, >= might all produce some Unicode weirdness upon
> > inadequate collations.
> >
> >
> > If time doesn't allow, then don't spend effort on this, it's simply a
> > curiosity. :)
> >
> > Thanks!
> > Ryan
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
In reply to this post by R Smith
whaw... I could never come up with solutions like this. Will this work with
SQLite?
Where can I read more about this?

Op zo 24 jun. 2018 om 20:29 schreef R Smith <[hidden email]>:

>
>
> On 2018/06/22 11:05 PM, Dan Kennedy wrote:
> > On 06/23/2018 03:52 AM, R Smith wrote:
> >>
> >> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>   All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and
> >>> the F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for
> >> brevity, but you can easily change the "< 4" to "< 11" or "<= 10"
> >> according to preference.
> >
> > What would the window-function query be?
>
> Mainly one can avoid the self-join with a windowing function, like this:
>
> SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY
> F1) AS C, F2, F1) AS XWHERE X.C <= 10
>
> or
>
> SELECT F2, F1
>    FROM (
>       SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1
> DESC) AS R
>    ) AS X
> WHERE R <= 10
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

R Smith-2

On 2018/06/24 9:30 PM, Gert Van Assche wrote:
> whaw... I could never come up with solutions like this. Will this work with
> SQLite?
> Where can I read more about this?

I've only shown that query since Dan asked about it - it's not an SQLite
query.

To answer/comment on your questions:

1. Sure you can come up with it if you have read about it and understand
what it is intended for - There is nothing you can't know, only things
you don't know yet. :)

2. This will not work in SQLite currently - it's part of a group of
functions called "Windowing" functions built into the bigger DB engines
which do not care about their "Liteness".
"Windowing functions" is basically a set of functions that treats the
current ROW and its immediate preceding or acceding rows, or set of
grouped rows within a bigger query (grouping by whatever common aspect
you choose) as a unit and can return localized aggregates over them.
The ranking/row position functions prove especially handy when wanting
queries to easily produce row orders, ranks, etc. - but I promise you,
they all are very possible in SQLite without Windowing functions, just
not quite as obvious (as this thread has shown). More difficult to do
are things like Percentile or Running-Total, though those too can be
accomplished with a self-join or correlated sub-query in SQLite.
The example in the original mail is from MSSQL, but similar queries work
in Postgres, Oracle, etc.

3. You can read more by Googling "RDBMS Windowing functions" - or just
click here: http://google.com/?q=RDBMS+windowing+functions


Quick intro to Windowing in SQL (Apologies to all, the following is NOT
for SQLite [yet]):

Say I have a list of students in three classrooms and their test scores,
and I would like to show the listing ranked by their scores, per class.


Sorting makes part of the problem easy:

SELECT class, student, score
   FROM students
  ORDER BY class, student, score DESC
;  -- This works in SQLite

This might produce a list like:

class, student, score
A5, Ann,     94
A5, Able,    88
A5, Adrian,  67
B5, James,   92
B5, Jenny,   88
B5, John,    87
B5, Joan,    74
C5, Sloan,   98
C5, Sean,    79

Now a common thing is to want to put a rank integer number in front of
every classmate so it is easy to see position, like this (spaced for
legibility only):

rank, class, student, score
1, A5, Ann,     94
2, A5, Able,    88
3, A5, Adrian,  67

1, B5, James,   92
2, B5, Jenny,   88
3, B5, John,    87
4, B5, Joan,    74

1, C5, Sloan,   98
2, C5, Sean,    79

And for that the query will have to produce the whole list (no overall
grouping) but then group these students by "class" to figure out the
rank within every class "window" of the  larger query.

You could say in English: We need to show the RANK *over* every window
(aka "set of records") that is *partitioned by* the "class" field, where
the rank within each "class" is determined by the value of the "score"
field in a *descending order*.  That's easy enough to understand (I hope!).

All that remains is to translate that to SQL. Looking at the English
phrase above and the SQL below should quickly clarify how it all fits
together:

SELECT RANK() OVER (PARTITION BY class ORDER BY score DESC) as rank,
class, student, score
   FROM students
  ORDER BY class, student, score DESC
;  -- This example is MSSQL format


If you don't care about the rank, and simply wish to number the rows in
some order (student name for example), you could opt for something like:

SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY student) as
student_no, class, student, score
   FROM students
  ORDER BY class, student
;  -- MSSQL again

which will output this:

student_no, class, student, score
1, A5, Able,    88
2, A5, Adrian,  67
3, A5, Ann,     94
1, B5, James,   92
2, B5, Jenny,   88
3, B5, Joan,    74
4, B5, John,    87
1, C5, Sean,    79
2, C5, Sloan,   98

Of course the aggregate doesn't need to be a rank or row number, it can
simply be a count() or Avg() or any aggregate function you fancy.

Lastly, if I now want to only see the top 2 ranked students of every
class (which is like your original question in this thread), I can
simply have the RANK query above as an inner query and in the outer
query add "... WHERE rank <= 2; "[1].

rank, class, student, score
1, A5, Ann,     94
2, A5, Able,    88
1, B5, James,   92
2, B5, Jenny,   88
1, C5, Sloan,   98
2, C5, Sean,    79


Now you know. :)
Cheers,
Ryan


[1] - Rank is only useful as a value if the order-by is of a unique
field (or combination of fields). Consider if the top 5 students had
scores like 95, 88, 88, 88 and 75. The RANK() function over them would
produce ranks 1, 2, 2, 2, 5 - because technically the 3 middle scores
are all the same and so all ranked equally 2nd, while the 5th score is
3rd highest and will be ranked either 5th or 3rd based on whether you
use RANK() [5th] or DENSE_RANK() [3rd] functions.

To change this to a solid numbered list, it is best to use ROW_NUMBER()
which forces unique follow-on integers without "holes" in them to once
again produce 1,2,3,4 and 5.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
Very interesting,  Ryan. Thanks for sharing.

If I would like to use these ranking techniques in SQLite, can I do this
with an extension? Has nobody created a Windowing.dll yet?
Just asking. My short term need has been solved, but now that I know this,
I'm sure I'd like to use this technique later. But I like to stick to
SQLite.

Gert

Op ma 25 jun. 2018 om 01:00 schreef R Smith <[hidden email]>:

>
> On 2018/06/24 9:30 PM, Gert Van Assche wrote:
> > whaw... I could never come up with solutions like this. Will this work
> with
> > SQLite?
> > Where can I read more about this?
>
> I've only shown that query since Dan asked about it - it's not an SQLite
> query.
>
> To answer/comment on your questions:
>
> 1. Sure you can come up with it if you have read about it and understand
> what it is intended for - There is nothing you can't know, only things
> you don't know yet. :)
>
> 2. This will not work in SQLite currently - it's part of a group of
> functions called "Windowing" functions built into the bigger DB engines
> which do not care about their "Liteness".
> "Windowing functions" is basically a set of functions that treats the
> current ROW and its immediate preceding or acceding rows, or set of
> grouped rows within a bigger query (grouping by whatever common aspect
> you choose) as a unit and can return localized aggregates over them.
> The ranking/row position functions prove especially handy when wanting
> queries to easily produce row orders, ranks, etc. - but I promise you,
> they all are very possible in SQLite without Windowing functions, just
> not quite as obvious (as this thread has shown). More difficult to do
> are things like Percentile or Running-Total, though those too can be
> accomplished with a self-join or correlated sub-query in SQLite.
> The example in the original mail is from MSSQL, but similar queries work
> in Postgres, Oracle, etc.
>
> 3. You can read more by Googling "RDBMS Windowing functions" - or just
> click here: http://google.com/?q=RDBMS+windowing+functions
>
>
> Quick intro to Windowing in SQL (Apologies to all, the following is NOT
> for SQLite [yet]):
>
> Say I have a list of students in three classrooms and their test scores,
> and I would like to show the listing ranked by their scores, per class.
>
>
> Sorting makes part of the problem easy:
>
> SELECT class, student, score
>    FROM students
>   ORDER BY class, student, score DESC
> ;  -- This works in SQLite
>
> This might produce a list like:
>
> class, student, score
> A5, Ann,     94
> A5, Able,    88
> A5, Adrian,  67
> B5, James,   92
> B5, Jenny,   88
> B5, John,    87
> B5, Joan,    74
> C5, Sloan,   98
> C5, Sean,    79
>
> Now a common thing is to want to put a rank integer number in front of
> every classmate so it is easy to see position, like this (spaced for
> legibility only):
>
> rank, class, student, score
> 1, A5, Ann,     94
> 2, A5, Able,    88
> 3, A5, Adrian,  67
>
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, John,    87
> 4, B5, Joan,    74
>
> 1, C5, Sloan,   98
> 2, C5, Sean,    79
>
> And for that the query will have to produce the whole list (no overall
> grouping) but then group these students by "class" to figure out the
> rank within every class "window" of the  larger query.
>
> You could say in English: We need to show the RANK *over* every window
> (aka "set of records") that is *partitioned by* the "class" field, where
> the rank within each "class" is determined by the value of the "score"
> field in a *descending order*.  That's easy enough to understand (I hope!).
>
> All that remains is to translate that to SQL. Looking at the English
> phrase above and the SQL below should quickly clarify how it all fits
> together:
>
> SELECT RANK() OVER (PARTITION BY class ORDER BY score DESC) as rank,
> class, student, score
>    FROM students
>   ORDER BY class, student, score DESC
> ;  -- This example is MSSQL format
>
>
> If you don't care about the rank, and simply wish to number the rows in
> some order (student name for example), you could opt for something like:
>
> SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY student) as
> student_no, class, student, score
>    FROM students
>   ORDER BY class, student
> ;  -- MSSQL again
>
> which will output this:
>
> student_no, class, student, score
> 1, A5, Able,    88
> 2, A5, Adrian,  67
> 3, A5, Ann,     94
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, Joan,    74
> 4, B5, John,    87
> 1, C5, Sean,    79
> 2, C5, Sloan,   98
>
> Of course the aggregate doesn't need to be a rank or row number, it can
> simply be a count() or Avg() or any aggregate function you fancy.
>
> Lastly, if I now want to only see the top 2 ranked students of every
> class (which is like your original question in this thread), I can
> simply have the RANK query above as an inner query and in the outer
> query add "... WHERE rank <= 2; "[1].
>
> rank, class, student, score
> 1, A5, Ann,     94
> 2, A5, Able,    88
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 1, C5, Sloan,   98
> 2, C5, Sean,    79
>
>
> Now you know. :)
> Cheers,
> Ryan
>
>
> [1] - Rank is only useful as a value if the order-by is of a unique
> field (or combination of fields). Consider if the top 5 students had
> scores like 95, 88, 88, 88 and 75. The RANK() function over them would
> produce ranks 1, 2, 2, 2, 5 - because technically the 3 middle scores
> are all the same and so all ranked equally 2nd, while the 5th score is
> 3rd highest and will be ranked either 5th or 3rd based on whether you
> use RANK() [5th] or DENSE_RANK() [3rd] functions.
>
> To change this to a solid numbered list, it is best to use ROW_NUMBER()
> which forces unique follow-on integers without "holes" in them to once
> again produce 1,2,3,4 and 5.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Olivier Mascia
> Le 26 juin 2018 à 07:56, Gert Van Assche <[hidden email]> a écrit :
>
> If I would like to use these ranking techniques in SQLite, can I do this
> with an extension? Has nobody created a Windowing.dll yet?
> Just asking. My short term need has been solved, but now that I know this,
> I'm sure I'd like to use this technique later. But I like to stick to
> SQLite.

Don't want to spoil any news, it is viewable by the public anyway, but there are clearly experimental, interesting (and significant) work ongoing by the SQLite developers around SQL windowing functions.  A quick look to the exp-window-functions branch clearly shows that.

https://www.sqlite.org/src/timeline?n=100&r=exp-window-functions

So it _may_ be possible that you see these features in a later version of SQLite, or not.  We'll see.  I certainly wish the best for this experience which looks good and nicely ongoing.

From experience as a user of SQLite, not all development of features through branches get merged to the trunk.  Sometimes they stay available as a branch for you to choose to use, sometimes they end up in the main SQLite product (trunk branch).  Sometimes they can stay significant time aside before one day being merged.  I have no specific expectations, and you shouldn't have too, I'm just monitoring areas of development that are interesting to my eyes and programming needs.  Mainly the 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this 'exp-window-functions' branch.

:)

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Gert Van Assche-2
Thanks Olivier, very good to know.

gert

Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia <[hidden email]>:

> > Le 26 juin 2018 à 07:56, Gert Van Assche <[hidden email]> a écrit :
> >
> > If I would like to use these ranking techniques in SQLite, can I do this
> > with an extension? Has nobody created a Windowing.dll yet?
> > Just asking. My short term need has been solved, but now that I know
> this,
> > I'm sure I'd like to use this technique later. But I like to stick to
> > SQLite.
>
> Don't want to spoil any news, it is viewable by the public anyway, but
> there are clearly experimental, interesting (and significant) work ongoing
> by the SQLite developers around SQL windowing functions.  A quick look to
> the exp-window-functions branch clearly shows that.
>
> https://www.sqlite.org/src/timeline?n=100&r=exp-window-functions
>
> So it _may_ be possible that you see these features in a later version of
> SQLite, or not.  We'll see.  I certainly wish the best for this experience
> which looks good and nicely ongoing.
>
> From experience as a user of SQLite, not all development of features
> through branches get merged to the trunk.  Sometimes they stay available as
> a branch for you to choose to use, sometimes they end up in the main SQLite
> product (trunk branch).  Sometimes they can stay significant time aside
> before one day being merged.  I have no specific expectations, and you
> shouldn't have too, I'm just monitoring areas of development that are
> interesting to my eyes and programming needs.  Mainly the
> 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this
> 'exp-window-functions' branch.
>
> :)
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT

Richard Rousselot
Just wanted to support the idea of adding windowing functions to SQLite as
it is the biggest hurdle for me to get others to use it.  If size is an
issue, I would love to have a build option to enable it.

My $0.02

On Tue, Jun 26, 2018 at 8:27 AM Gert Van Assche <[hidden email]> wrote:

> Thanks Olivier, very good to know.
>
> gert
>
> Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia <[hidden email]>:
>
> > > Le 26 juin 2018 à 07:56, Gert Van Assche <[hidden email]> a écrit :
> > >
> > > If I would like to use these ranking techniques in SQLite, can I do
> this
> > > with an extension? Has nobody created a Windowing.dll yet?
> > > Just asking. My short term need has been solved, but now that I know
> > this,
> > > I'm sure I'd like to use this technique later. But I like to stick to
> > > SQLite.
> >
> > Don't want to spoil any news, it is viewable by the public anyway, but
> > there are clearly experimental, interesting (and significant) work
> ongoing
> > by the SQLite developers around SQL windowing functions.  A quick look to
> > the exp-window-functions branch clearly shows that.
> >
> > https://www.sqlite.org/src/timeline?n=100&r=exp-window-functions
> >
> > So it _may_ be possible that you see these features in a later version of
> > SQLite, or not.  We'll see.  I certainly wish the best for this
> experience
> > which looks good and nicely ongoing.
> >
> > From experience as a user of SQLite, not all development of features
> > through branches get merged to the trunk.  Sometimes they stay available
> as
> > a branch for you to choose to use, sometimes they end up in the main
> SQLite
> > product (trunk branch).  Sometimes they can stay significant time aside
> > before one day being merged.  I have no specific expectations, and you
> > shouldn't have too, I'm just monitoring areas of development that are
> > interesting to my eyes and programming needs.  Mainly the
> > 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and
> this
> > 'exp-window-functions' branch.
> >
> > :)
> >
> > --
> > Best Regards, Meilleures salutations, Met vriendelijke groeten,
> > Olivier Mascia
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12