CODEWITHSUNDEEP
sqlpostgresql
Yorma
Yorma

Reputation: 11

PostgreSQL: Create a series of numbers unique for a reference

I made an example: http://sqlfiddle.com/#!17/0f948/9

1-to many is what I'm building, although many-to-many would be more real life for this example.

What I need to accomplish: I should be able to generate author order depicting sequence of integers for the authororder-column in author-table, starting from number 1 so that every number is unique amongst the book-reference. So for every author set under a certain book-reference the numbers from 1 to 2, to 3 etc. Also the numbers for authororder should be generated to reflect the order of the author.fullname (as: 'select * from author order by fullname').

I tried using sequence, but is does not reset the sequence number for each book as I would like it to, instead the number continues to increase (as it should I guess). I also tried doing this with the help of row_number(), but could not manage to prepare a script which would achieve what I want it to.

The end result after generating the authororder would be something like this:

author_id       fullname          mobileno            fk_book_id           authororder
100           John Green            30303                 1                     (1)
101           Maureen Johnson       4343                  1                     (3)
102           Lauren Myracle        76665                 1                     (2)
103           Greg Mortenson        6434                  2                     (2)
104           David Oliver Relin    72322                 2                     (1)
105           Marco Polo            54321                 3                     (3)
106           Angus MacGyver        27234                 3                     (1)
107           Timo TA               83451                 3                     (4)
108           Anonymous             55554                 3                     (2)

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You seem to want:

row_number() over (partition by fk_book_id order by fullname) as authororder

You can incorporate this into an update as:

update author a
    set authororder = new_authororder
    from (select a.*, 
                 row_number() over (partition by fk_book_id order by fullname) as new_authororder
          from author a
         ) aa
    where a.authorid = aa.authorid;

Upvotes: 1

Related Questions