SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL and row number

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •