How about: drop table if exists temp.ordered; create temporary table ordered (a number not null); insert into ordered select a from table order by a; select avg(a) from ordered group by rowid/100; drop table if exists temp.ordered;
--- () ascii ribbon campaign against html e-mail /\ 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 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 (SELECT * FROM table ORDER BY a); > > and found that ROWID is null, not the record number as I expected. I > cannot modify my example adding ROWID to the nested select, as in > > SELECT AVG(a) FROM (SELECT ROWID, a FROM table ORDER BY a) group by ROWID/10; > > as the ROWIDs would be in disorder. > > Thus my question, what is the correct way of solving my problem? > > Best regards, > > Luis > > > -- > > o > W. Luis Mochán, | tel:(52)(777)329-1734 /<(*) > Instituto de Ciencias Físicas, UNAM | fax:(52)(777)317-5388 `>/ /\ > Apdo. Postal 48-3, 62251 | (*)/\/ \ > Cuernavaca, Morelos, México | moc...@fis.unam.mx /\_/\__/ > > O< ascii ribbon campaign - stop html mail - www.asciiribbon.org > > > _______________________________________________ > 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
