Possible limitations of temporary tables vs views: 1) Temporary tables are static - views are dynamic - i.e. anytime a change is made to the database the temporary tables might need to be recreated.
2) Memory: Temporary tables will be retained in memory until dropped. Views will only use memory when used. Eli Adler -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Robert Citek Sent: Saturday, March 28, 2009 12:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sequential row numbers from query That would work. In fact, my current solution, which actually pipes to perl, works pretty well. It's just that I have to then import the data back into the database. So, I'd prefer to do the process entirely in SQL. I was thinking maybe a view, but that didn't work. Apparently, there is no rowid with views (or is there something equivalent that I'm overlooking?). $ sqlite3 db 'create view bar as select * from foo order by field desc; select rowid, * from bar ; ' |c |b |a Substituting a temporary table for the view works: $ sqlite3 db 'create temporary table bat as select * from foo order by field desc; select rowid, * from bat ; ' 1|c 2|b 3|a Any limitations to consider when using a temporary table? Any other ideas? Regards, - Robert On Fri, Mar 27, 2009 at 3:37 PM, Thomas Briggs <[email protected]> wrote: > Holy cow that feels inefficient. > > It's a bit clunky, but why not insert into a temporary table, > ordered as desired, and then use the rowid from the temp table? _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the [email protected] and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. To protect the environment please do not print this e-mail unless necessary. NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, United Kingdom. A company registered in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
