Try having a look at post "How to determine player's leaderboard rank efficiently?" (24th August) this could give you idea on how to solve this problem through temporary tables. Regards Il 16/set/2014 14:14 "Paul Sanderson" <sandersonforens...@gmail.com> ha scritto:
> _rowid_ is probably the answer with a temporary table > > On 16 September 2014 13:00, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > Thanks - I like the temporary table idea, but now sure how it would work. > > > > say for instance I have two tables > > > > create tab1 (id int, name text) > > 1, 'paul' > > 2, 'helen' > > 3, 'melanie' > > create tab2 (id int, country text) > > 1, 'uk' > > 2, 'scotland' > > > > I can create a temporary table > > create table tab3 as select * from tab1, tab2 > > > > and I get values > > > > 1|paul|1|uk > > 1|paul|2|scotland > > 2|helen|1|uk > > 2|helen|2|scotland > > 3|melanie|1|uk > > 3|melanie|2|scotland > > > > what I want is > > > > 1|1|paul|1|uk > > 2|1|paul|2|scotland > > 3|2|helen|1|uk > > 4|2|helen|2|scotland > > 5|3|melanie|1|uk > > 6|3|melanie|2|scotland > > > > How do I get that extra column? > > > > Cheers > > Paul > > > > > > > > > > > > On 16 September 2014 12:26, Dave Wellman <dwell...@ward-analytics.com> > > wrote: > > > >> Hi, > >> If you can insert into another table then you might want to use a > trigger > >> on the target table. I've done that with good effect (only on low > volumes > >> though, I don't know what would happen on larger volume and if you have > >> larger volumes). > >> Cheers, > >> Dave > >> > >> > >> 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 > >> Registered company number: 3917021 Registered in England and Wales. > >> > >> > >> -----Original Message----- > >> From: sqlite-users-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 .... 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 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 > >> > skype: r3scue193 > >> > twitter: @sandersonforens > >> > Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 > >> > 572786 > >> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - > >> > Deleted SQLite recovery > >> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC > >> > processing made easy _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > Paul > > www.sandersonforensics.com > > skype: r3scue193 > > twitter: @sandersonforens > > Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 > > 572786 > > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - > > Deleted SQLite recovery > > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC > > processing made easy > > > > > > > -- > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 > 572786 > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - > Deleted SQLite recovery > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC > processing made easy > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
