Re: [sqlite] Create join and add unique column

bject: Re: [sqlite] Create join and add unique column > > >On 2014/09/16 15:32, Paul Sanderson wrote: >> select _rowid_, * from tab3 does the trick - thanks all > >Indeed, and if you are pedantic or do not work in a table with rowids, >the solution is to explicitly give the tabl

Re: [sqlite] Create join and add unique column

On Sep 16, 2014, at 8:15 PM, RSmith wrote: > could you show how to achieve this in SQL via the ranking method you linked Well, ranking is the same as numbering, no? So, for example: with NameSet as ( select 1 as id, 'paul' as name union all select 2 as id, 'helen'

Re: [sqlite] Create join and add unique column

It all helped me thanks - just not in solving this particular problem (which I have now manage to do) Thanks all. On 16 September 2014 19:33, jose isaias cabrera wrote: > > "RSmith" wrote... > > >> On 2014/09/16 15:32, Paul Sanderson wrote: >> >>> select _rowid_, *

Re: [sqlite] Create join and add unique column

"RSmith" wrote... On 2014/09/16 15:32, Paul Sanderson wrote: select _rowid_, * from tab3 does the trick - thanks all Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this:

Re: [sqlite] Create join and add unique column

On 2014/09/16 20:00, James K. Lowden wrote: Or not use a temporary table. http://www.schemamania.org/sql/#rank.rows Now easier with CTE. In theory it could be *faster* than a temporary table, because the insertion I/O is avoided. But only testing will tell. --jkl Hi James, I'm

Re: [sqlite] Create join and add unique column

On Tue, 16 Sep 2014 16:38:09 +0200 RSmith wrote: > On 2014/09/16 15:32, Paul Sanderson wrote: > > select _rowid_, * from tab3 does the trick - thanks all > > Indeed, and if you are pedantic or do not work in a table with > rowids, the solution is to explicitly give the table

Re: [sqlite] Create join and add unique column

Back to the drawing board. Create table as creates the columns with types based on their declared affinity - I particularly need to identify columns in the new table that existed as blobs in the original tables, create table as gives these an affinity of "", I really need the original type

Re: [sqlite] Create join and add unique column

Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :( I think I am getting there On 16 September 2014 15:38, RSmith wrote: > > On 2014/09/16 15:32, Paul Sanderson

Re: [sqlite] Create join and add unique column

On 2014/09/16 15:32, Paul Sanderson wrote: select _rowid_, * from tab3 does the trick - thanks all Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this: CREATE TEMPORARY TABLE

Re: [sqlite] Create join and add unique column

; Ward Analytics Ltd - information in motion >>> Tel: +44 (0) 118 9740191 >>> Fax: +44 (0) 118 9740192 >>> www: http://www.ward-analytics.com >>> >>> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, >>> United Kingdom, GU1 3SR >&

Re: [sqlite] Create join and add unique column

Fax: +44 (0) 118 9740192 > >> www: http://www.ward-analytics.com > >> > >> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, > >> United Kingdom, GU1 3SR > >> Registered company number: 3917021 Registered in England and Wales. > >> &

Re: [sqlite] Create join and add unique column

740192 >> www: http://www.ward-analytics.com >> >> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, >> United Kingdom, GU1 3SR >> Registered company number: 3917021 Registered in England and Wales. >> >> >> -----Original Message-

Re: [sqlite] Create join and add unique column

tember 2014 12:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Create join and add unique column > > could create a temporary table with a incrementing key and 'insert into > temp_table select join ...' something like that? > maybe use the existing key

Re: [sqlite] Create join and add unique column

-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker Sent: 16 September 2014 12:02 To: General Discussion of SQLite Database Subject: Re: [sqlite] Create join and add unique column could create a temporary table with a incrementing key and 'insert into temp_table select

Re: [sqlite] Create join and add unique column

On Tue, Sep 16, 2014 at 5:51 AM, Paul Sanderson wrote: > I want to create a join on two tables and add a unique number to each > returned row. Can this be done with a SQL query? > > Thanks > -- > Paul > www.sandersonforensics.com Just a bit of thinking out loud, but

Re: [sqlite] Create join and add unique column

could create a temporary table with a incrementing key and 'insert into temp_table select join ...' something like that? maybe use the existing keys and create a composite key sorta thing? On Tue, Sep 16, 2014 at 3:51 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I want to