If you create your Ordering column in table B as type INTEGER PRIMARY
KEY, you can do:
INSERT INTO tableB (value)
SELECT value
FROM tableA
ORDER BY ...
And when you're done, the value of tableB.Ordering will represent the
order in which the rows were inserted into the table, a.k.a. the order
of the rows returned by the query. Note however that this really only
does what you want when tableB is initially empty - because what the
Ordering column really represents is the order in which rows were
inserted into the table, you won't get the results you looking for if
there are already rows in tableB. This is a fairly easy problem to
solve using intermediate tables though.
-Tom
> -----Original Message-----
> From: Brown, Dave [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 19, 2005 1:53 AM
> To: sqlite-users@sqlite.org
> 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
>
