Re: [sqlite] record number after reordering

2012-07-16 Thread Keith Medcalf
> On 7/16/2012 4:19 PM, Luis Mochan wrote: > > What are the pros and cons of this approach vs. using a temporal table as > suggested by Keith? > My technique doesn't require a temp table so may be somewhat easier to > use. On the other hand, I suspect it might be much slower on a large table. >

Re: [sqlite] record number after reordering

2012-07-16 Thread Igor Tandetnik
On 7/16/2012 4:19 PM, Luis Mochan wrote: What are the pros and cons of this approach vs. using a temporal table as suggested by Keith? My technique doesn't require a temp table so may be somewhat easier to use. On the other hand, I suspect it might be much slower on a large table. -- Igor Ta

Re: [sqlite] record number after reordering

2012-07-16 Thread Luis Mochan
Thanks Igor, What are the pros and cons of this approach vs. using a temporal table as suggested by Keith? Best regards, Luis On Sun, Jul 15, 2012 at 06:50:44PM -0400, Igor Tandetnik wrote: > Luis Mochan wrote: > > I want to reorder a table and then group and average it's values. I > > tried s

Re: [sqlite] record number after reordering

2012-07-15 Thread Igor Tandetnik
Luis Mochan wrote: > I want to reorder a table and then group and average it's values. I > tried something similar to > > SELECT AVG(a) FROM (SELECT a FROM table ORDER BY a) group by ROWID/10; > > in order to take the average 'a' for groups of 10 succesive values. Something like this perhaps:

Re: [sqlite] record number after reordering

2012-07-15 Thread Luis Mochan
Thanks Keith! Creating a temporal table with it's own rowid solved the problem. Luis On Sun, Jul 15, 2012 at 10:48:32AM -0600, Keith Medcalf wrote: > How about: > > drop table if exists temp.ordered; > create temporary table ordered (a number not null); > insert into ordered select a from table o

Re: [sqlite] record number after reordering

2012-07-15 Thread Keith Medcalf
/\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Luis Mochan > Sent: Saturday, 14 July, 2012 12:59 > To: sqlite-users@sqlite.org > Subject: [sqlite] record number after reordering > > I

[sqlite] record number after reordering

2012-07-15 Thread Luis Mochan
I want to reorder a table and then group and average it's values. I tried something similar to SELECT AVG(a) FROM (SELECT a FROM table ORDER BY a) group by ROWID/10; in order to take the average 'a' for groups of 10 succesive values. My example fails. To understand I tried SELECT ROWID FROM (SEL