Re: [sqlite] Can this be done with SQLite

[Keith Medcalf]

If you want it fast even for huge tables then force the creation of an index on 
the temporary rotate table:

begin immediate;
create temporary table rotate as
select oldkey,
   newkey,
   (select value from x where key = newkey) as value
  from (select key as oldkey,
   coalesce((select min(x1.key)
   from x as x1
  where x1.key > x.key), (select min(key)
from x)) as newkey
  from x
  order by key) as xx;
create unique index temp.idxRotate on rotate (oldkey);
update x
   set value = (select value from temp.rotate where oldkey=x.key);
drop table temp.rotate;
commit;


---
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: Keith Medcalf [mailto:kmedc...@dessus.com]
>Sent: Monday, 22 January, 2018 18:00
>To: 'SQLite mailing list'
>Subject: RE: [sqlite] Can this be done with SQLite
>
>Completely generic solution.  You change the direction of rotation by
>changing the SQL that builds your temp table ...
>
>sqlite> create table x (key integer primary key, value);
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> .head on
>sqlite> .mode column
>
>select * from x order by key;
>
>key   value
>  
>-6374565986553047082  -6292999241545120883
>-5989898834901854520  -9081225235206840749
>-5612633422423030496  -3498480116044899177
>-3719342152283010731  -4268175217960688953
>-2423950719408034905  -3377215796687069970
>3673537119323620073900645503222593618
>883226292009397075-1973921941627299252
>1707896441609026036   -2722166238737751675
>6339993451314418730   -8055191930500241295
>8898575339909083958   7883859772702047363
>
>select oldkey,
>   newkey,
>   (select value from x where key = newkey) as value
>  from (select key as oldkey,
>   coalesce((select min(x1.key)
>   from x as x1
>  where x1.key > x.key), (select min(key)
>from x)) as newkey
>  from x
>  order by key) as xx;
>
>oldkeynewkeyvalue
>    
>-6374565986553047082  -5989898834901854520  -9081225235206840749
>-5989898834901854520  -5612633422423030496  -3498480116044899177
>-5612633422423030496  -3719342152283010731  -4268175217960688953
>-3719342152283010731  -2423950719408034905  -3377215796687069970
>-2423950719408034905  3673537119323620073900645503222593618
>367353711932362007883226292009397075-1973921941627299252
>8832262920093970751707896441609026036   -2722166238737751675
>1707896441609026036   6339993451314418730   -8055191930500241295
>6339993451314418730   8898575339909083958   7883859772702047363
>8898575339909083958   -6374565986553047082  -6292999241545120883
>
>begin immediate;
>create temporary table rotate as
>select oldkey,
>   newkey,
>   (select value from x where key = newkey) as value
>  from (select key as oldkey,
>   coalesce((select min(x1.key)
>   from x as x1
>  where x1.key > x.key), (select min(key)
>from x)) as newkey
>  from x
>  order by key) as xx;
>update x
>   set value = (select value from temp.rotate where oldkey=x.key);
>drop table temp.rotate;
>commit;
>
>select * from x order by key;
>
>key   value
>  
>-6374565986553047082  -9081225235206840749
>-5989898834901854520  -3498480116044899177
>-5612633422423030496  -4268175217960688953
>-3719342152283010731  -3377215796687069970
>-2423950719408034905  3900645503222593618
>367353711932362007-1973921941627299252
>883226292009397075-2722166238737751675
>1707896441609026036   -8055191930500241295
>6339993451314418730   7883859772702047363
>8898575339909083958   -6292999241545120883
>
>---
>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-us

Re: [sqlite] Can this be done with SQLite

[Keith Medcalf]

Completely generic solution.  You change the direction of rotation by changing 
the SQL that builds your temp table ...

sqlite> create table x (key integer primary key, value);
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> .head on
sqlite> .mode column

select * from x order by key;

key   value
  
-6374565986553047082  -6292999241545120883
-5989898834901854520  -9081225235206840749
-5612633422423030496  -3498480116044899177
-3719342152283010731  -4268175217960688953
-2423950719408034905  -3377215796687069970
3673537119323620073900645503222593618
883226292009397075-1973921941627299252
1707896441609026036   -2722166238737751675
6339993451314418730   -8055191930500241295
8898575339909083958   7883859772702047363

select oldkey,
   newkey,
   (select value from x where key = newkey) as value
  from (select key as oldkey, 
   coalesce((select min(x1.key) 
   from x as x1 
  where x1.key > x.key), (select min(key) from x)) as 
newkey 
  from x
  order by key) as xx;

oldkeynewkeyvalue
    
-6374565986553047082  -5989898834901854520  -9081225235206840749
-5989898834901854520  -5612633422423030496  -3498480116044899177
-5612633422423030496  -3719342152283010731  -4268175217960688953
-3719342152283010731  -2423950719408034905  -3377215796687069970
-2423950719408034905  3673537119323620073900645503222593618
367353711932362007883226292009397075-1973921941627299252
8832262920093970751707896441609026036   -2722166238737751675
1707896441609026036   6339993451314418730   -8055191930500241295
6339993451314418730   8898575339909083958   7883859772702047363
8898575339909083958   -6374565986553047082  -6292999241545120883

begin immediate;
create temporary table rotate as 
select oldkey,
   newkey,
   (select value from x where key = newkey) as value
  from (select key as oldkey, 
   coalesce((select min(x1.key) 
   from x as x1 
  where x1.key > x.key), (select min(key) from x)) as 
newkey 
  from x
  order by key) as xx;
update x
   set value = (select value from temp.rotate where oldkey=x.key);
drop table temp.rotate;
commit;

select * from x order by key;

key   value
  
-6374565986553047082  -9081225235206840749
-5989898834901854520  -3498480116044899177
-5612633422423030496  -4268175217960688953
-3719342152283010731  -3377215796687069970
-2423950719408034905  3900645503222593618
367353711932362007-1973921941627299252
883226292009397075-2722166238737751675
1707896441609026036   -8055191930500241295
6339993451314418730   7883859772702047363
8898575339909083958   -6292999241545120883

---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>Sent: Monday, 22 January, 2018 17:19
>To: SQLite mailing list
>Subject: Re: [sqlite] Can this be done with SQLite
>
>2018-01-23 1:02 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>:
>
>>
>> Part of the problem is going to be that you have not defined the
>problem
>> sufficiently for a "solution" to be proposed.  Based on your
>somewhat silly
>> example one can deduce the following constraints:
>>
>> With respect to "key":
>>  - this is TEXT (UTF-8 or something else)?
>>  - you specify check(length(key)) == 1  do you mean:
>>- one character in some encoding (key between 0 and
>0x)
>>- one byte? (ie, ord(key) between 0 and 255)
>>- something else entirely?
>>- is it contiguous?
>>- if not contiguous what are the rules defining the non-
>contiguousness?
>>
>
>​I would like a general solution. So the type of key is not defined
>and it
>is not necessary to be contiguous.
>​
>
>
>
>>  - what is the "rotation order" based on?
>>- next arithmetic value, upper wraps to lower?
>>- next used key (by some collation order?  Which collation
>order?)
>>   

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

2018-01-23 1:02 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>:

>
> Part of the problem is going to be that you have not defined the problem
> sufficiently for a "solution" to be proposed.  Based on your somewhat silly
> example one can deduce the following constraints:
>
> With respect to "key":
>  - this is TEXT (UTF-8 or something else)?
>  - you specify check(length(key)) == 1  do you mean:
>- one character in some encoding (key between 0 and 0x)
>- one byte? (ie, ord(key) between 0 and 255)
>- something else entirely?
>- is it contiguous?
>- if not contiguous what are the rules defining the non-contiguousness?
>

​I would like a general solution. So the type of key is not defined and it
is not necessary to be contiguous.
​



>  - what is the "rotation order" based on?
>- next arithmetic value, upper wraps to lower?
>- next used key (by some collation order?  Which collation order?)
>- based on "used" values?
>- based on "entire domain"?
>

​Rotation is either up or down. In my example it was up. (In my perception.)

Order is just the default order.
​



> The problem and its solution is rather simple, once you define problem to
> be solved with sufficient specificity to permit a solution.
>
> Your "example" below does not provide sufficient referents to generate a
> solution that is cohesive over any problem domain other than that covered
> by the example, and your referential constraints are inadequate to ensure
> integrity for your limited example.
>

​I think I can solve it generally. I will look into it and share it here.
​



> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> >Sent: Monday, 22 January, 2018 13:30
> >To: SQLite mailing list
> >Subject: [sqlite] Can this be done with SQLite
> >
> >I have the following table:
> >CREATE TABLE playYouTubeVideo (
> >key TEXTNOT NULL,
> >speed   FLOAT   NOT NULL,
> >
> >CONSTRAINT key   CHECK(length(key)  == 1),
> >CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
> >
> >PRIMARY KEY(key)
> >);
> >
> >
> >Say I want to rotate a part:
> >- The value by key '1' becomes the value by key '2'.
> >​- The value by key '2' becomes the value by key '3'.
> >​- The value by key '3' becomes the value by key '4'.
> >- The value by key '4' becomes the value by key '5'.
> >- The value by key '5' becomes the value by key '6'.
> >
> >​I suppose that I need to do this programmatically, or can this be
> >done
> >with SQL?
> >
> >And optionally also:
> >​- The value by key '1' becomes the value by key '5'.
>

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Keith Medcalf]

Part of the problem is going to be that you have not defined the problem 
sufficiently for a "solution" to be proposed.  Based on your somewhat silly 
example one can deduce the following constraints:

With respect to "key":
 - this is TEXT (UTF-8 or something else)?
 - you specify check(length(key)) == 1  do you mean:
   - one character in some encoding (key between 0 and 0x)
   - one byte? (ie, ord(key) between 0 and 255)
   - something else entirely?
   - is it contiguous?
   - if not contiguous what are the rules defining the non-contiguousness?
 
 - what is the "rotation order" based on?
   - next arithmetic value, upper wraps to lower?
   - next used key (by some collation order?  Which collation order?)
   - based on "used" values?
   - based on "entire domain"?

The problem and its solution is rather simple, once you define problem to be 
solved with sufficient specificity to permit a solution.  

Your "example" below does not provide sufficient referents to generate a 
solution that is cohesive over any problem domain other than that covered by 
the example, and your referential constraints are inadequate to ensure 
integrity for your limited example.

Your example could be used to generate a solution set if and only if you 
changed the constraints such that:

  check(key) between '1' and '5'
and
  count(key) must be 5 (ie, there must be five records comprising exactly each 
key once).

Otherwise there is insufficient information to formulate a solution.

---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>Sent: Monday, 22 January, 2018 13:30
>To: SQLite mailing list
>Subject: [sqlite] Can this be done with SQLite
>
>I have the following table:
>CREATE TABLE playYouTubeVideo (
>key TEXTNOT NULL,
>speed   FLOAT   NOT NULL,
>
>CONSTRAINT key   CHECK(length(key)  == 1),
>CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
>PRIMARY KEY(key)
>);
>
>
>Say I want to rotate a part:
>- The value by key '1' becomes the value by key '2'.
>​- The value by key '2' becomes the value by key '3'.
>​- The value by key '3' becomes the value by key '4'.
>- The value by key '4' becomes the value by key '5'.
>- The value by key '5' becomes the value by key '6'.
>
>​I suppose that I need to do this programmatically, or can this be
>done
>with SQL?
>
>And optionally also:
>​- The value by key '1' becomes the value by key '5'.
>​
>--
>Cecil Westerhof
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Don V Nielsen]

Cecil, you need to make a backup of the values before they are altered, and
then alter them from the backup. That way, by the time you have come full
circle, you are not working with the changed values.


On Mon, Jan 22, 2018 at 4:11 PM, Cecil Westerhof 
wrote:

> 2018-01-22 23:07 GMT+01:00 Igor Tandetnik :
>
> > On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
> >
> >>
> >> When I do this, I get:
> >> sqlite> SELECT *
> >> ...> FROM   playYouTubeVideo
> >> ...> WHERE  key BETWEEN '1' AND '5'
> >> ...> ;
> >> 1|1.0
> >> 2|2.0
> >> 3|3.0
> >> 4|4.0
> >> 5|5.0
> >>
> >> [snip]
> >>
> >> sqlite> SELECT *
> >> ...> FROM   playYouTubeVideo
> >> ...> WHERE  key BETWEEN '1' AND '5'
> >> ...> ;
> >> 1|2.0
> >> 2|3.0
> >> 3|4.0
> >> 4|5.0
> >> 5|2.0
> >>
> >> But I want the last one needs to be 1.0.
> >>
> >
> > Something along these lines, perhaps:
> >
> > update playYouTubeVideo set key=char(61440+unicode(key));
> > update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> > then '5' else char(unicode(key)-61440-1) end;
> >
>
> ​This also expects the values to be constant. But what I want is that the
> record with key 1 gets the value from key 2, with key 2 from key 3, …
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

2018-01-22 23:15 GMT+01:00 David Raymond <david.raym...@tomtom.com>:

> Ok, so you're looking for a "rotate" sort of thing?
>

​Yes. ;-)
​



> (The schema with a text key with length of 1 made me think it wasn't going
> to get too big)
>

​In this particular case it is a string with length 1, but I am 'always'
looking at the general case.
​



> Are the keys all integers then? All positive? Continuous?
>

​In this case yes, but it does not need to be.
​



> begin transaction;
> create temp table t (key int primary key, speed real);
> insert into t select key, (select t1.speed from playYouTubeVideo as t1
> where t1.key = foo.key % 5 + 1) from playYouTubeVideo;
> update playYouTubeVideo set speed = (select speed from t where key =
> playYouTubeVideo.key);
> drop table t;
> commit;
>

​Comes a good end in the right direction, but I am thinking I am going to
do it programmatically​. Maybe write a general function for it.

Everyone thanks for the fast replies.



> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 4:37 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Can this be done with SQLite
>
> 2018-01-22 21:38 GMT+01:00 David Raymond <david.raym...@tomtom.com>:
>
> > Unless I'm reading you wrong then just do the normal
> >
> > begin transaction;
> > update playYouTubeVideo set speed = (
> > select speed from playYouTubeVideo where key = '2')
> > where key = '1';
> > update playYouTubeVideo set speed = (
> > select speed from playYouTubeVideo where key = '3')
> > where key = '2';
> > ...
> > update playYouTubeVideo set speed = (
> > select speed from playYouTubeVideo where key = '5')
> > where key = '1';
> > commit;
> >
>
> ​Nope. By the way I see that I did not write it correctly. :'-(
>
> When I do this, I get:
> sqlite> SELECT *
>...> FROM   playYouTubeVideo
>...> WHERE  key BETWEEN '1' AND '5'
>...> ;
> 1|1.0
> 2|2.0
> 3|3.0
> 4|4.0
> 5|5.0
> sqlite> begin transaction;
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '2')
>...> where key = '1';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '3')
>...> where key = '2';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '4')
>...> where key = '3';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '5')
>...> where key = '4';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '1')
>...> where key = '5';
> sqlite> commit;
> sqlite> SELECT *
>...> FROM   playYouTubeVideo
>...> WHERE  key BETWEEN '1' AND '5'
>...> ;
> 1|2.0
> 2|3.0
> 3|4.0
> 4|5.0
> 5|2.0
>
> But I want the last one needs to be 1.0.
> Also, when the range becomes big, it will be a lot of code.
>
>
> I was hoping I overlooked a smart trick, but I probably need to do it
> programmatically.
>
>
> -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Cecil Westerhof
> > Sent: Monday, January 22, 2018 3:30 PM
> > To: SQLite mailing list
> > Subject: [sqlite] Can this be done with SQLite
> >
> > I have the following table:
> > CREATE TABLE playYouTubeVideo (
> > key TEXTNOT NULL,
> > speed   FLOAT   NOT NULL,
> >
> > CONSTRAINT key   CHECK(length(key)  == 1),
> > CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
> >
> > PRIMARY KEY(key)
> > );
> >
> >
> > Say I want to rotate a part:
> > - The value by key '1' becomes the value by key '2'.
> > ​- The value by key '2' becomes the value by key '3'.
> > ​- The value by key '3' becomes the value by key '4'.
> > - The value by key '4' becomes the value by key '5'.
> > - The value by key '5' becomes the value by key '6'.
> >
> > ​I suppose that I need to do this programmatically, or can this be done
> > with SQL?
> >
> > And optionally also:
> > ​- The value by key '1' becomes the value by key '5'.
> > ​
> > --
> > Cecil Westerhof
> > ___
> > sqlite-users mailing list
> > sqlite-users@ma

Re: [sqlite] Can this be done with SQLite

[David Raymond]

Ok, so you're looking for a "rotate" sort of thing?

(The schema with a text key with length of 1 made me think it wasn't going to 
get too big)

Are the keys all integers then? All positive? Continuous?

begin transaction;
create temp table t (key int primary key, speed real);
insert into t select key, (select t1.speed from playYouTubeVideo as t1 where 
t1.key = foo.key % 5 + 1) from playYouTubeVideo;
update playYouTubeVideo set speed = (select speed from t where key = 
playYouTubeVideo.key);
drop table t;
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list
Subject: Re: [sqlite] Can this be done with SQLite

2018-01-22 21:38 GMT+01:00 David Raymond <david.raym...@tomtom.com>:

> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '2')
> where key = '1';
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

When I do this, I get:
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it
programmatically.


-Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [sqlite] Can this be done with SQLite
>
> I have the following table:
> CREATE TABLE playYouTubeVideo (
> key TEXTNOT NULL,
> speed   FLOAT   NOT NULL,
>
> CONSTRAINT key   CHECK(length(key)  == 1),
> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
> PRIMARY KEY(key)
> );
>
>
> Say I want to rotate a part:
> - The value by key '1' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be done
> with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Marc L. Allen]

Two options  (one based on Igor's answer)

update playYouTubVideo 
set speed = case when key = 1 then (select speed from playYouTubeVIdeo when key 
= 2) 
  when key = 2 then (select ... when key = 3)
 ...
 when key = 5 then (select ... when key = 1)

I can't recall if the update will actually do that atomically (so that the 
speed for key = 1 is still available).

Option 2:

Since I believe SQLite doesn't support UPDATE FROM, you'll need temporary 
variables somewhere.

If the table is small enough, copy it off and do the updates using the copy as 
a source.  If the table is large, then maybe add in an extra staging field in 
the record?

CREATE TABLE playYouTubeVideo (
key TEXTNOT NULL,
speed   FLOAT   NOT NULL,
tmpSpeed FLOAT NULL,
CONSTRAINT key   CHECK(length(key)  == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
PRIMARY KEY(key)
);

Then before doing the cycle,

update playYouTubeVideo set tmpSpeed = speed

Then perform the update as David suggested, but using the tmpSpeed variable.

You can probably optimize that by using just tmpSpeed variable from the start 
or end of the cycle.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Can this be done with SQLite

2018-01-22 21:38 GMT+01:00 David Raymond <david.raym...@tomtom.com>:

> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '2')
> where key = '1';
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

When I do this, I get:
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it 
programmatically.


-Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [sqlite] Can this be done with SQLite
>
> I have the following table:
> CREATE TABLE playYouTubeVideo (
> key TEXTNOT NULL,
> speed   FLOAT   NOT NULL,
>
> CONSTRAINT key   CHECK(length(key)  == 1),
> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
> PRIMARY KEY(key)
> );
>
>
> Say I want to rotate a part:
> - The value by key '1' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be 
> done with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

2018-01-22 23:07 GMT+01:00 Igor Tandetnik :

> On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
>
>>
>> When I do this, I get:
>> sqlite> SELECT *
>> ...> FROM   playYouTubeVideo
>> ...> WHERE  key BETWEEN '1' AND '5'
>> ...> ;
>> 1|1.0
>> 2|2.0
>> 3|3.0
>> 4|4.0
>> 5|5.0
>>
>> [snip]
>>
>> sqlite> SELECT *
>> ...> FROM   playYouTubeVideo
>> ...> WHERE  key BETWEEN '1' AND '5'
>> ...> ;
>> 1|2.0
>> 2|3.0
>> 3|4.0
>> 4|5.0
>> 5|2.0
>>
>> But I want the last one needs to be 1.0.
>>
>
> Something along these lines, perhaps:
>
> update playYouTubeVideo set key=char(61440+unicode(key));
> update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> then '5' else char(unicode(key)-61440-1) end;
>

​This also expects the values to be constant. But what I want is that the
record with key 1 gets the value from key 2, with key 2 from key 3, …

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Igor Tandetnik]

On 1/22/2018 4:36 PM, Cecil Westerhof wrote:


When I do this, I get:
sqlite> SELECT *
...> FROM   playYouTubeVideo
...> WHERE  key BETWEEN '1' AND '5'
...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0

[snip]

sqlite> SELECT *
...> FROM   playYouTubeVideo
...> WHERE  key BETWEEN '1' AND '5'
...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.


Something along these lines, perhaps:

update playYouTubeVideo set key=char(61440+unicode(key));
update playYouTubeVideo set key=case when key=char(61440+unicode('1')) then '5' 
else char(unicode(key)-61440-1) end;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

2018-01-22 22:36 GMT+01:00 Jim Morris <j...@bearriver.com>:

> Wouldn't the mod operator do this?
>
> Do an update and set key = 1 + (5 + key)%5


​Only when the values are: 1.0, 2.0, 3.0, 4.0 and 5.0.
But not when they are: 1.25, 1.5, 1.75, 2.0 and 1.0.


On 1/22/2018 12:38 PM, David Raymond wrote:
>
>> Unless I'm reading you wrong then just do the normal
>>
>> begin transaction;
>> update playYouTubeVideo set speed = (
>>  select speed from playYouTubeVideo where key = '2')
>>  where key = '1';
>> update playYouTubeVideo set speed = (
>>  select speed from playYouTubeVideo where key = '3')
>>  where key = '2';
>> ...
>> update playYouTubeVideo set speed = (
>>  select speed from playYouTubeVideo where key = '5')
>>  where key = '1';
>> commit;
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Cecil Westerhof
>> Sent: Monday, January 22, 2018 3:30 PM
>> To: SQLite mailing list
>> Subject: [sqlite] Can this be done with SQLite
>>
>> I have the following table:
>> CREATE TABLE playYouTubeVideo (
>>  key TEXTNOT NULL,
>>  speed   FLOAT   NOT NULL,
>>
>>  CONSTRAINT key   CHECK(length(key)  == 1),
>>  CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>>
>>  PRIMARY KEY(key)
>> );
>>
>>
>> Say I want to rotate a part:
>> - The value by key '1' becomes the value by key '2'.
>> ​- The value by key '2' becomes the value by key '3'.
>> ​- The value by key '3' becomes the value by key '4'.
>> - The value by key '4' becomes the value by key '5'.
>> - The value by key '5' becomes the value by key '6'.
>>
>> ​I suppose that I need to do this programmatically, or can this be done
>> with SQL?
>>
>> And optionally also:
>> ​- The value by key '1' becomes the value by key '5'.
>> ​
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

2018-01-22 21:38 GMT+01:00 David Raymond <david.raym...@tomtom.com>:

> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '2')
> where key = '1';
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

When I do this, I get:
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it
programmatically.


-Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [sqlite] Can this be done with SQLite
>
> I have the following table:
> CREATE TABLE playYouTubeVideo (
> key TEXTNOT NULL,
> speed   FLOAT   NOT NULL,
>
> CONSTRAINT key   CHECK(length(key)  == 1),
> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
> PRIMARY KEY(key)
> );
>
>
> Say I want to rotate a part:
> - The value by key '1' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be done
> with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Jim Morris]

Wouldn't the mod operator do this?

Do an update and set key = 1 + (5 + key)%5


On 1/22/2018 12:38 PM, David Raymond wrote:

Unless I'm reading you wrong then just do the normal

begin transaction;
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '2')
 where key = '1';
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '3')
 where key = '2';
...
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '5')
 where key = '1';
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 3:30 PM
To: SQLite mailing list
Subject: [sqlite] Can this be done with SQLite

I have the following table:
CREATE TABLE playYouTubeVideo (
 key TEXTNOT NULL,
 speed   FLOAT   NOT NULL,

 CONSTRAINT key   CHECK(length(key)  == 1),
 CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),

 PRIMARY KEY(key)
);


Say I want to rotate a part:
- The value by key '1' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[David Raymond]

Unless I'm reading you wrong then just do the normal

begin transaction;
update playYouTubeVideo set speed = (
select speed from playYouTubeVideo where key = '2')
where key = '1';
update playYouTubeVideo set speed = (
select speed from playYouTubeVideo where key = '3')
where key = '2';
...
update playYouTubeVideo set speed = (
select speed from playYouTubeVideo where key = '5')
where key = '1';
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 3:30 PM
To: SQLite mailing list
Subject: [sqlite] Can this be done with SQLite

I have the following table:
CREATE TABLE playYouTubeVideo (
key TEXTNOT NULL,
speed   FLOAT   NOT NULL,

CONSTRAINT key   CHECK(length(key)  == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),

PRIMARY KEY(key)
);


Say I want to rotate a part:
- The value by key '1' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​
-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done with SQLite

[Cecil Westerhof]

I have the following table:
CREATE TABLE playYouTubeVideo (
key TEXTNOT NULL,
speed   FLOAT   NOT NULL,

CONSTRAINT key   CHECK(length(key)  == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),

PRIMARY KEY(key)
);


Say I want to rotate a part:
- The value by key '1' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​
-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-09 12:55 AM, K. P. wrote:
> Brilliant - thanks.Though I still do not understand my 
> errordistinct(t.LastName || ', ' || t.FirstName),Seems to me that I am 
> passing a single argument in parentheses to distinct

You had a separator (DISTINCT ..   , '; ') as a second argument. I 
removed this and added it to the aggregate name. Hope that clears it up!


>
>> To: sqlite-users at mailinglists.sqlite.org
>> From: rsmith at rsweb.co.za
>> Date: Fri, 9 Oct 2015 00:38:10 +0200
>> Subject: Re: [sqlite] Can this be done in SQLite?
>>
>>
>>
>> On 2015-10-09 12:22 AM, K. P. wrote:
>>> I tried this, of course, before asking, but:
>>> group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers,
>>> gives
>>> [15:19:32] Error while executing SQL query on database 'test': DISTINCT 
>>> aggregates must have exactly one argument
>> As the error suggests, it can't have more than one argument (parameter),
>> so this should work:
>>
>> group_concat(distinct (t.LastName || ', ' || t.FirstName || '; ')) As 
>> Teachers,
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>   
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-09 12:22 AM, K. P. wrote:
> I tried this, of course, before asking, but:
> group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers,
> gives
> [15:19:32] Error while executing SQL query on database 'test': DISTINCT 
> aggregates must have exactly one argument

As the error suggests, it can't have more than one argument (parameter), 
so this should work:

group_concat(distinct (t.LastName || ', ' || t.FirstName || '; ')) As Teachers,

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 11:47 PM, K. P. wrote:
> Am using SQLiteStudio - which I really like - though I have wondered if it 
> passes all its knowledge around errors onto the user...

I hope it does. Try some other ones too, just to get a wider idea. 
Perhaps http://www.sqlc.rifin.co.za/ or http://www.sqliteexpert.com/ if 
on Windows, or https://github.com/sqlitebrowser/sqlitebrowser if on 
Linux/Mac.  If any of them do things that seem odd, and you are 
comfortable using command line tools, the sqlite3 CLI is best for 
checking/testing SQL at http://www.sqlite.org/download.html

As to your distinct question, sure, use like this:

SELECT group_concat(DISTINCT fullName) FROM sometable WHERE 1;

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 11:30 PM, K. P. wrote:
> It does indeed work - had to make myself a simpler query than the real life 
> one to see this. I probably had some other syntax error that I mistook for 
> it...
> Thanks!

Nice.
Are you logging the SQLite errors and error descriptions? It's usually 
quite good at explaining where exactly your query goes wrong.

[sqlite] Can this be done in SQLite?

[R.Smith]

On 2015-10-08 10:54 PM, K. P. wrote:
> Thanks for that.I'd need something along the lines of
>
> group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName,
>
>
> which in itself does not seem to be supported.

This would be quite a normal group concatenation and works perfectly well...

Why do you believe it doesn't work or isn't supported?


> c.LastName || ', ' || c.FirstName, ';') as FullName,
> group_concat(FullName, ';') as ClientName,

Ok, this won't work, but not for the reasons you think. An alias cannot 
be referenced in the same select header as it is instantiated. You can 
however achieve this with a CTE (among other ways), something like this 
(I made up A and B here cause I don't know the rest of your schema):

WITH cte1(A, B, FullName) AS (
   SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
cWHERE 1
)
SELECT A, B, group_concat(FullName, '; ')
FROM cte1
  WHERE 1
  GROUP BY A, B
  ORDER BY A, B

Perhaps you don't wish to have names repeated, in which case this will 
work better:

WITH cte1(A, B, FullName) AS (
   SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
cWHERE 1
)
SELECT A, B, group_concat(DISTINCT FullName)
FROM cte1
  WHERE 1
  GROUP BY A, B
  ORDER BY A, B



If you have some SQL that doesn't seem to work, kindly post your table 
schema and the full SQL you are trying to do, that way we can form a 
better idea of what you aim to do and provide more complete answers.

Cheers!
Ryan

[sqlite] Can this be done in SQLite?

[K. P.]

Brilliant - thanks.Though I still do not understand my errordistinct(t.LastName 
|| ', ' || t.FirstName),Seems to me that I am passing a single argument in 
parentheses to distinct

> To: sqlite-users at mailinglists.sqlite.org
> From: rsmith at rsweb.co.za
> Date: Fri, 9 Oct 2015 00:38:10 +0200
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> 
> On 2015-10-09 12:22 AM, K. P. wrote:
> > I tried this, of course, before asking, but:
> > group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers,
> > gives
> > [15:19:32] Error while executing SQL query on database 'test': DISTINCT 
> > aggregates must have exactly one argument
> 
> As the error suggests, it can't have more than one argument (parameter), 
> so this should work:
> 
> group_concat(distinct (t.LastName || ', ' || t.FirstName || '; ')) As 
> Teachers,
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[Simon Slavin]

On 8 Oct 2015, at 10:47pm, K. P.  wrote:

> Am using SQLiteStudio - which I really like - though I have wondered if it 
> passes all its knowledge around errors onto the user...

For such experimentation I recommend the SQLite command line tool.  It's the 
thinnest practical shell around the raw SQLite API, and written by the SQLite 
developer team.  If it produces an error you're 100% sure that this is the 
error generated by the API.

GUI tools like SQLiteStudio are fine ... for routine or simple organisations 
that you understand.  I just wouldn't use it for learning something new.

Simon.

[sqlite] Can this be done in SQLite?

[Simon Slavin]

On 8 Oct 2015, at 9:54pm, K. P.  wrote:

> Thanks for that.I'd need something along the lines of
> 
> group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName, 
> 
> 
> which in itself does not seem to be supported.

Why not ?  What is the complete SELECT command ?  What happens when you try it ?

If it really isn't valid ...

Create a VIEW which returns the individual concatenations.  Then use 
group_concat() in a SELECT on the VIEW.

Simon.

[sqlite] Can this be done in SQLite?

[K. P.]

I tried this, of course, before asking, but:
group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers, 
gives
[15:19:32] Error while executing SQL query on database 'test': DISTINCT 
aggregates must have exactly one argument

> To: sqlite-users at mailinglists.sqlite.org
> From: rsmith at rsweb.co.za
> Date: Thu, 8 Oct 2015 23:58:09 +0200
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> 
> On 2015-10-08 11:47 PM, K. P. wrote:
> > Am using SQLiteStudio - which I really like - though I have wondered if it 
> > passes all its knowledge around errors onto the user...
> 
> I hope it does. Try some other ones too, just to get a wider idea. 
> Perhaps http://www.sqlc.rifin.co.za/ or http://www.sqliteexpert.com/ if 
> on Windows, or https://github.com/sqlitebrowser/sqlitebrowser if on 
> Linux/Mac.  If any of them do things that seem odd, and you are 
> comfortable using command line tools, the sqlite3 CLI is best for 
> checking/testing SQL at http://www.sqlite.org/download.html
> 
> As to your distinct question, sure, use like this:
> 
> SELECT group_concat(DISTINCT fullName) FROM sometable WHERE 1;
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[K. P.]

Almost there - just one issue left:
I now receive a list of students per class - perfect.
I also do a group_concat on the teachers (as a class could be taught by more 
than one) and so I I get the same teacher repeated a number of times at times.
Any way I can apply a 'distinct' here?

> To: sqlite-users at mailinglists.sqlite.org
> From: rsmith at rsweb.co.za
> Date: Thu, 8 Oct 2015 23:34:05 +0200
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> 
> On 2015-10-08 11:30 PM, K. P. wrote:
> > It does indeed work - had to make myself a simpler query than the real life 
> > one to see this. I probably had some other syntax error that I mistook for 
> > it...
> > Thanks!
> 
> Nice.
> Are you logging the SQLite errors and error descriptions? It's usually 
> quite good at explaining where exactly your query goes wrong.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[K. P.]

Am using SQLiteStudio - which I really like - though I have wondered if it 
passes all its knowledge around errors onto the user...

> To: sqlite-users at mailinglists.sqlite.org
> From: rsmith at rsweb.co.za
> Date: Thu, 8 Oct 2015 23:34:05 +0200
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> 
> On 2015-10-08 11:30 PM, K. P. wrote:
> > It does indeed work - had to make myself a simpler query than the real life 
> > one to see this. I probably had some other syntax error that I mistook for 
> > it...
> > Thanks!
> 
> Nice.
> Are you logging the SQLite errors and error descriptions? It's usually 
> quite good at explaining where exactly your query goes wrong.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[K. P.]

It does indeed work - had to make myself a simpler query than the real life one 
to see this. I probably had some other syntax error that I mistook for it...
Thanks!

> To: sqlite-users at mailinglists.sqlite.org
> From: rsmith at rsweb.co.za
> Date: Thu, 8 Oct 2015 23:19:53 +0200
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> 
> On 2015-10-08 10:54 PM, K. P. wrote:
> > Thanks for that.I'd need something along the lines of
> >
> > group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName,
> >
> >
> > which in itself does not seem to be supported.
> 
> This would be quite a normal group concatenation and works perfectly well...
> 
> Why do you believe it doesn't work or isn't supported?
> 
> 
> > c.LastName || ', ' || c.FirstName, ';') as FullName,
> > group_concat(FullName, ';') as ClientName,
> 
> Ok, this won't work, but not for the reasons you think. An alias cannot 
> be referenced in the same select header as it is instantiated. You can 
> however achieve this with a CTE (among other ways), something like this 
> (I made up A and B here cause I don't know the rest of your schema):
> 
> WITH cte1(A, B, FullName) AS (
>SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
> cWHERE 1
> )
> SELECT A, B, group_concat(FullName, '; ')
> FROM cte1
>   WHERE 1
>   GROUP BY A, B
>   ORDER BY A, B
> 
> Perhaps you don't wish to have names repeated, in which case this will 
> work better:
> 
> WITH cte1(A, B, FullName) AS (
>SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS 
> cWHERE 1
> )
> SELECT A, B, group_concat(DISTINCT FullName)
> FROM cte1
>   WHERE 1
>   GROUP BY A, B
>   ORDER BY A, B
> 
> 
> 
> If you have some SQL that doesn't seem to work, kindly post your table 
> schema and the full SQL you are trying to do, that way we can form a 
> better idea of what you aim to do and provide more complete answers.
> 
> Cheers!
> Ryan
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[K. P.]

Thanks for that.I'd need something along the lines of

group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName, 


which in itself does not seem to be supported.


c.LastName || ', ' || c.FirstName, ';') as FullName, 
group_concat(FullName, ';') as ClientName, 


also not :(


Any way around this?





> From: slavins at bigfraud.org
> Date: Thu, 8 Oct 2015 17:01:06 +0100
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Can this be done in SQLite?
> 
> 
> On 8 Oct 2015, at 4:59pm, K. P.  wrote:
> 
> > Given the following tables, is it possible to extract rows of class data 
> > along with all participating student names concatenated in one column in a 
> > single SQL query?
> 
> See the 'group_concat()' function:
> 
> <https://www.sqlite.org/lang_aggfunc.html>
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can this be done in SQLite?

[Hody Crouch]

You should be able to concatenate the first and last name in a subquery and
then use group_concat to output the single column you describe.

For example:

select group_concat(clientname, ';') from (select FirstName || ',' ||
LastName from yourtable);

On Thu, Oct 8, 2015 at 4:54 PM, K. P.  wrote:

> Thanks for that.I'd need something along the lines of
>
> group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName,
>
>
> which in itself does not seem to be supported.
>
>
> c.LastName || ', ' || c.FirstName, ';') as FullName,
> group_concat(FullName, ';') as ClientName,
>
>
> also not :(
>
>
> Any way around this?
>
>
>
>
>
> > From: slavins at bigfraud.org
> > Date: Thu, 8 Oct 2015 17:01:06 +0100
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] Can this be done in SQLite?
> >
> >
> > On 8 Oct 2015, at 4:59pm, K. P.  wrote:
> >
> > > Given the following tables, is it possible to extract rows of class
> data along with all participating student names concatenated in one column
> in a single SQL query?
> >
> > See the 'group_concat()' function:
> >
> > <https://www.sqlite.org/lang_aggfunc.html>
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Can this be done in SQLite?

[Simon Slavin]

On 8 Oct 2015, at 4:59pm, K. P.  wrote:

> Given the following tables, is it possible to extract rows of class data 
> along with all participating student names concatenated in one column in a 
> single SQL query?

See the 'group_concat()' function:



Simon.

[sqlite] Can this be done in SQLite?

[K. P.]

Given the following tables, is it possible to extract rows of class data along 
with all participating student names concatenated in one column in a single SQL 
query?

Classes---IDDateSubject

ParticipantsIDClassIDStudentID

StudentsIDName
As always, thanks for any help on this!