Thread: Numbering rows

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Numbering rows


    Hi,


    is there a way to insert numbering into a table WITHOUT USING A STORED PROCEDURE?


    like i have an existing table and i want the NR column have values from 1 to N (the number of rows in the table)


    Can i do that with a simple UPDATE command? How?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    You can use EXECUTE BLOCK to write a code just like stored procedure and execute it once or you can write a trigger active before update/insert + generator. If you write a trigger it will help you with the future records when you insert them.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    Thank you for your answer.

    That sounds really good. Could you please provide an example? I never used that EXECUTE BLOCK thingy.

    Let's say this is the table structure:

    NR (integer)
    DATA (anything)


    How would the numbering block look like, and how can i execute it?


    PS: The trigger+generator is not an option here because i often need to renumber a filtered set of records.
    Last edited by nagysz; October 25th, 2013 at 02:41 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    You can execute Execute block as you execute any other select statement.
    It's just a block of PSQL code in normal query.
    In Delphi I use TIBSQL and its ExecQuery method.
    Code:
    EXECUTE BLOCK
    AS
    DECLARE VARIABLE L_COUNTER INTEGER;
    DECLARE VARIABLE L_PRIMARY_KEY PRIMARY_KEY_TYPE;
    BEGIN
        L_COUNTER = 1;
    
        FOR SELECT
           PRIMARY_KEY
        FROM
           TABLE
        INTO
           :L_PRIMARY_KEY
        DO BEGIN
    
           UPDATE TABLE SET
               NR = :L_COUNTER
           WHERE
               PRIMARY_KEY = :L_PRIMARY_KEY;
    
           L_COUNTER = L_COUNTER + 1;
    
        END
    END

    Comments on this post

    • nagysz agrees
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Or you can use this code into a trigger_BI:

    DECLARE VARIABLE L_COUNTER INTEGER;
    BEGIN
    select max(nr) from your_table_here into :lcounter;
    new.nr = lcounter+1;
    END
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    In upper post @nagysz said that the trigger is not an option.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0
    Create a generator then in your stored procedure where you want your ID value use gen_id(generator_name, 1) which will create an autoincrementing number for the field in the stored procedure.

    Remember to reset the generator at the end if you want them to always start at 1.

    So :

    CREATE SEQUENCE My_ID;
    ALTER SEQUENCE My_ID RESTART WITH 0;

    then the select would be along the lines of :

    SELECT GEN_ID(MY_ID, 1) FROM RDB$DATABASE

    and then to reset to start :

    ALTER SEQUENCE My_ID RESTART WITH 0;

    If the actual number is irrelevant but the record for ordering then you can ignore the second alter sequence
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    The alternative for "SELETC GEN_ID(..." is much simpler: variable = NEXT VALUE FOR sequence_name; No selects at all.
    The question in the first post is how to do this without using stored procedures or triggers.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0

    Thumbs up That's useful


    Originally Posted by mIRCata
    The alternative for "SELETC GEN_ID(..." is much simpler: variable = NEXT VALUE FOR sequence_name; No selects at all.
    The question in the first post is how to do this without using stored procedures or triggers.
    Never new about the NEXT VALUE FOR command

    Always nice to learn something new

IMN logo majestic logo threadwatch logo seochat tools logo