SQL and row number

[jkh1978]

I want to store part of a table in a new table, but also store the order of the select...

if my select is SELECT ID FROM PERSON

I thought I could do

INSERT INTO TEMP SELECT ID FROM PERSON

but I also want to store a "counter", so I can later retrieve the values from TEMP and guarantee the same order...

TEMP should look like

ID | ROWNUM

where ROWNUM is a counter.

Is there a function I can add to my select statement?

[r937]

best would be to create the table ahead of time, inlcuding an auto_increment field, using an ordinary CREATE TABLE statement, then run an INSERT INTO ... SELECT ...

mysql has non-standard optional syntax CREATE TABLE SELECT so you can do both in one step, and it does let you declare an auto_increment at the same time -- it works, but i've not been able to avoid getting an extra null column in there

rudy