INSERT INTO tableB ( value, ordering ) SELECT value, count(*) FROM tableA WHERE tableA.value > 4 GROUP BY value ;
notes the "group by" clauses ... ----- Original Message ----- From: "Brown, Dave" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Thursday, May 19, 2005 1:52 PM Subject: [sqlite] How to get row numbers in a query? > > Is there any way to SELECT out the row number of returned results in a > query? > For example: > > SELECT row_number(), value from some_table; > 1 ValueA > 2 ValueB > 3 ValueC > ... etc ... ?? > > What I really want this for is a query where I am inserting from table A > into table B, and table B has a column which stores the order the new rows > are inserted. So I need a statement like: > > INSERT INTO tableB (value, ordering) SELECT value, row_number() FROM tableA > WHERE tableA.value > 4; > > I want this to result in the following being added to tableB: > Value | Ordering > ---------------- > 5 1 > 8 2 > 9 3 > ....etc.... > > Any ideas? > > -Dave >
